Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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, which means 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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Have you tried doing the Sum of Table B outside the expression as one of the Group By columns rather than doing an expression? I think there are issues with doing an expression in a GroupBy where one of the components of the expression is a Group By or Sum. Do you get the right result if you do it as two queries? If so you may want to explore using a subquery.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for your suggestion WendellB. Using two queries really isn't an option, but I did finally get it to work.

    By moving the condition for TABLE_B Date to the Join statement instead of the WHERE statement, the query works properly.

    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 <= 20090115
    WHERE TABLE_A.DATE1 <= 20090115
    GROUP BY TABLE_A.ITEM, TABLE_A.AMOUNT

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Interesting approach - I've not seen that approach used very much, but if it works that's great. I take it this is not Access, but another database server?
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •