Several joins including self on a data table and two look up tables 2

[vladk]

Hi,

I am trying to build join query on Bank_Fees, Unit_Lookup, and Fee_Lookup tables:

Bank_Fees

Invoice_Mnth Invoice_Yr Unit_Name Fee_Type Amt
1	2013	Unit-1	A	12
1	2013	Unit-1	A	18
1	2013	Unit-1	B	13
1	2013	Unit-1	B	15
1	2013	Unit-2	A	10
1	2013	Unit-2	B	11
2	2013	Unit-3	A	5
2	2013	Unit-3	A	7
3	2013	Unit-3	A	6
3	2013	Unit-3	B	9
3	2013	Unit-3	B	4
...

[u]Unit_Lookup[/u]
Unit_Name
Unit-1
Unit-2
Unit-3
Unit-4
...

[u]Fee_Lookup[/u]
Fee_Type
A
B
C

The result of the query I am trying to receive would be like this:

Query for Invoice_Mnth = 3 and Invoice_Yr = 2013

[u]Unit_Name  Fee_Type	Amt[/u]

Unit-1	        A	NULL
Unit-1	        B	NULL
Unit-1	        C	NULL
Unit-2	        A	NULL
Unit-2	        B	NULL
Unit-2	        C	NULL
Unit-4	        A	NULL
Unit-4	        B	NULL
Unit-4	        C	NULL
Unit-3	        A	SUM(6)
Unit-3	        B	SUM(9,4)
Unit-3	        C	NULL

I built a query that lists missing Unit-4 only, and just once, one record, and lists Unit-3, total three records:

Unit-4	        NULL	NULL
Unit-3	        A	SUM(6)
Unit-3	        B	SUM(9,4)

but how to add the rest of the units and also list fee types with NULL amounts, I have no clue.

I am sure it can be done, but my SQL experience is quite limited...

Thank you for any help!

 

[BigRed1212]

So your query currently looks something like:

Select unit_name, fee_type, sum(amt)
from bank_fees
where invoice_mnth=3 and invoice_year=2013
group by unit_name, fee_type
order by unit_name, fee_type

??

Could you post it?

 

[PHV]

Something like this ?

SELECT A.Unit_Name,A.Fee_Type,B.SumofAmt AS Amt
FROM (
SELECT Unit_Name,Fee_Type FROM Unit_Lookup,Fee_Lookup
) A LEFT JOIN (
SELECT Unit_Name,Fee_Type,Sum(Amt) AS SumofAmt FROM Bank_Fees
WHERE Invoice_Mnth=3 AND Invoice_Yr=2013 GROUP BY Unit_Name,Fee_Type
) B ON A.Unit_Name=B.Unit_Name AND A.Fee_Type=B.Fee_Type

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vladk]

BigRed1212,

I can't post my query, it's at my work! But this is not really important, it was simple right outer join query. But thank you for your reply, I do appreciate it!

PHV,

When I only looked at your "name", I realized, that I will not need any other answers.

Your query works perfectly, and I need to figure out how and why it works!

If I could, I would give you 10 stars!

Thank you!

:0)

 

[BigRed1212]

I need to figure out how and why it works!"

PHV's subquery and my query are the same. PHV is just a LOT faster and better than I am. Anyway, the subquery sums up the values for amt for those records which meet your month and year criteria. The outer queries construct a list of all the unit and fee combos and then left join (to preserve that list) with the results of the subquery.

 

[vladk]

BigRed1212,

Thank you for your brilliant explanation!

:0)