Results 1 to 3 of 3

Thread: Left Join query

  1. #1
    Join Date
    May 2012
    Posts
    17

    Unanswered: Left Join query

    Being a dabbler I have been struggling with this for a while now and need some help.
    Scenario: I have 2 tables: Location tbl that stores location Id (lesss than 100 records nearly always the same) and Transactions tbl with locId, Date, Charges, Paymts. The objective is to display for each Location listed in Location tbl (parent tbl) the aggregate Charges and Payments between 2 dates. So far so good. The twist comes from a rebel record, LocationID# 3970.
    Loc 3970 is a fictitious location thus does not have related records in Transactions tbl. All other records in Location tbl are the sum of related records in Transactions tbl except for record 3970 that gets its total by aggregating all records prior a certain date in transactions

    query1: the following returns the aggregate amounts as I want for all records except record 3970
    SELECT p.LocatId1, p.Locname, Coalesce(Sum(d.charges),0) as TotCharges, Coalesce(Sum(d.Paymts),0) as TotPmts FROM Location p
    LEFT JOIN Transactions d ON p.LocatId1 = d.LocId AND
    CASE
    WHEN p.LocatId1 <= '3900' THEN ...a date range...
    WHEN p.LocatId1 > '4000' THEN ...a date range...
    END
    GROUP BY p.LocatId1

    query2: the following return the aggregate amounts as I want for record 3970
    SELECT sum(charges), sum(Paymts) FROM Transactions where LocId > '4000' and Date < '2009-09-30'

    The big question is how to merge the 2 queries; i.e. how to tell mysql to use query1 for all records except record 3970 and use query2 only for record 3970. Please note that this must be done from the Parent tbl. that's why I used Left Join.
    Thanks for any help
    Last edited by tomgreen; 05-07-12 at 15:06. Reason: clarification: above dates are provided are expl and will be provided by user at runtime

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Try to merge them with UNION, in this way:
    Code:
    SELECT p.LocatId1, p.Locname, Coalesce(Sum(d.charges),0) as TotCharges, Coalesce(Sum(d.Paymts),0) as TotPmts FROM Location p
    LEFT JOIN Transactions d ON p.LocatId1 = d.LocId AND
    CASE
    WHEN p.LocatId1 <= '3900' THEN ...a date range...
    WHEN p.LocatId1 > '4000' THEN ...a date range...
    END
    
    UNION ALL
    
    SELECT 3970 as LocatId1, 'fictitious location' as Locname,
               sum(charges), sum(Paymts) 
    FROM Transactions where LocId > '4000' and Date < '2009-09-30'
    
    GROUP BY LocatId1

  3. #3
    Join Date
    May 2012
    Posts
    17
    Thanks a million kordirko, that did it.

Posting Permissions

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