INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Problem with left outer join

Problem with left outer join

(OP)
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

RE: Problem with left outer join

The record

CODE

2    200.00    20090331
from TABLE_B doesn't fulfil the conditon

CODE

.. AND TABLE_B.DATE <= 20090115
 

RE: Problem with left outer join

(OP)
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?

RE: Problem with left outer join

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem with left outer join

(OP)
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.

 

RE: Problem with left outer join

(OP)
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.

RE: Problem with left outer join

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Problem with left outer join

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close