Problem with left outer join 2

[czab]

I need to join two tables to get the results I'm looking for. I've been working on this since yesterday and can't figure out whether or not I can achieve what I need in one query. I need to select ALL records that meet the DATE criteria from TABLE_A and ONLY the records that meet the DATE criteria from TABLE_B. TABLE_A only has one record per ITEM, but TABLE_B can have multiple records for the same ITEM, so I need to total the PAYMT field in TABLE_B.

My Data:
TABLE_A
ITEM AMOUNT DATE
1 300.00 20080605
2 200.00 20080605
3 100.00 20090215

TABLE_B
ITEM PAYMT DATE
1 200.00 20080910
1 50.00 20081017
1 50.00 20090331
2 200.00 20090331
3 100.00 20090815

My Query:
SELECT
TABLE_A.ITEM,
TABLE_A.AMOUNT,
TABLE_A.AMOUNT - SUM(TABLE_B.PAYMT) as REMAINING
FROM
TABLE_A LEFT OUTER JOIN TABLE_B ON TABLE_A.ITEM = TABLE_B.ITEM
WHERE
(TABLE_A.DATE <= 20090115 AND TABLE_B.DATE <= 20090115)
GROUP BY
TABLE_A.ITEM,
TABLE_A.AMOUNT


Expected result:
ITEM AMOUNT REMAINING
1 300.00 50.00
2 200.00 200.00

Actual result:
ITEM AMOUNT REMAINING
1 300.00 50.00

 

[mikrom]

The record

2    200.00    20090331

from TABLE_B doesn't fulfil the conditon

.. AND TABLE_B.DATE <= 20090115

 

[czab]

I understand what you're saying mikrom. I would have thought that for Item 2 the formula SUM(TABLE_B.PAYMT) would result in NULL, but the AMOUNT of 200.00 for ITEM #2 from TABLE_A would still be pulled in to the results and the REMAINING amount for ITEM 2 would be 200.00 - nothing or 200.00.

What I'm trying to accomplish for Item #2 is
AMOUNT = 200.00
SUM(PAYMT) = NULL (or zero somehow)
REMAINING = 200.00 - 0 = 200.00

I need the results to give me a record for Item #2. Am I trying to do something that's impossible? Or am I just going about it in the completely wrong direction?

 

[fredericofonseca]

try
SELECT
TABLE_A.ITEM,
TABLE_A.AMOUNT,
TABLE_A.AMOUNT - SUM(coalesce(TABLE_B.amount1,0)) as REMAINING
FROM
TABLE_A LEFT OUTER JOIN TABLE_B ON TABLE_A.ITEM = TABLE_B.ITEM
AND TABLE_B.DATE1 <= '2009/01/15'
where
TABLE_A.DATE1 <= '2009/01/15'
GROUP BY
TABLE_A.ITEM,
TABLE_A.AMOUNT

and then try to explain to us why this works and yours does not

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[czab]

Thank you! It worked brilliantly! As you can tell, I'm a novice at SQL programming. I've never used the coalesce function before.

I appreciate your help and will do my best to explain why this works where mine failed.

The way I had the query written it would only select records that matched the date condition in both files because the date conditions were in the WHERE statement. Moving the condition AND TABLE_B.DATE1 <= '2009/01/15' to the join allows the left inner join to work the way I hoped it would work and brings in all the records for TABLE_A as long as they meet the conditions in the WHERE clause.

By using coalesce in the select statement it produces a result whether or not there's a corresponding record in TABLE_B.

I'm so glad I posted here. I can stop pulling my hair out now.

 

[czab]

Correction:

Moving the condition AND TABLE_B.DATE1 <= '2009/01/15' to the join allows the left OUTER join to work the way I hoped it would work and brings in all the records for TABLE_A as long as they meet the conditions in the WHERE clause.

 

[fredericofonseca]

czab,

gave you a star as you did understood what the issue was, and then, unlike others, did bother posting here the explanation as I asked you.


as for coalesce. whenever you have a left/right join, you need to use them is most cases, as otherwise you get null values that will affect your queries. you can try removing the coalesce on this sample and see the difference

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[czab]

fredericofonseca,

I tested the query without using coalesce and only ITEM #1 had a value for REMAINING. ITEM #2 had a value for ITEM and AMOUNT, but REMAINING was NULL. If you didn't mention coalesce I would have spent today pulling my hair out trying to figure out why I didn't have a value for REMAINING on the other ITEMS.

Thanks so much for the valuable lessons.