Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: MAX in WHERE clause?

    Hi... bit of an odd one. I have a query that works, but I'm sure it's not right.

    For the purposes of this query, I've got three tables:
    Greetings
    Charities
    CharityPayments

    Each Greeting is tied to a Charity, as is each CharityPayment.

    I want to get a sum of all the Greetings on behalf of a Charity since their last payment date, and the payment date itself.

    Here's what I've got. Like I said - it seems to work, and pull out the right-looking values. But I've got to do two subqueries in it to drop the last payment date into the WHERE clause - it just seems a hideously inelegant way of using a value that I've already got in the SELECT clause as "MAX(cp.paymentToDate) AS lastPaymentToDate". Is is right, or is there a better way?

    Code:
    SELECT
    c.idCharities,
    c.charityName,
    COUNT(g.idGreetings) AS totalGreetings,
    MAX(cp.paymentToDate) AS lastPaymentToDate
    FROM Greetings g
    INNER JOIN Charities c ON g.idCharities = c.idCharities
    LEFT JOIN CharityPayments cp ON cp.idCharities = c.idCharities
    WHERE (
      (g.sendDate > (
        SELECT MAX(cp2.paymentToDate) AS lastPaymentToDate
        FROM CharityPayments cp2
        WHERE cp2.idCharities = c.idCharities
      )
    ) OR (
        SELECT MAX(cp3.paymentToDate) AS lastPaymentToDate
        FROM CharityPayments cp3
        WHERE cp3.idCharities = c.idCharities
      ) IS NULL)
    GROUP BY g.idCharities
    ORDER BY c.charityName;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me know if this works

    if it works, let me know if there's any part you don't understand
    Code:
    SELECT c.idCharities
         , c.charityName
         , COUNT(g.idGreetings) AS totalGreetings    
         , p.lastPaymentToDate
      FROM Charities AS c
    INNER 
      JOIN Greetings AS g 
        ON g.idCharities = c.idCharities
    LEFT OUTER
      JOIN ( SELECT idCharities
                  , MAX(paymentToDate) AS lastPaymentToDate
               FROM CharityPayments 
             GROUP
                 BY idCharities ) AS p
        ON p.idCharities = c.idCharities 
     WHERE g.sendDate > COALESCE(p.lastPaymentToDate,'1900-01-01')
    GROUP 
        BY c.charityName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Ok, scrub that.

    It's not working right. It's coming out with the wrong numbers.

    Currently, there is only one Charity with any Payments logged against it. There are two payments logged for that Charity. Since the most recent of these payment dates, there have been 12 Greetings for that Charity.

    As it stands, the query correctly pulls out one row for that Charity, correctly displays the latest of the two payment dates, and claims it's found not 12 but 24 Greetings.

    If I amend the query with:
    GROUP BY g.idCharities, cp.idCharityPayments

    then I'm back down to 12 Greetings - but it shows two rows for the Charity.

    How can I get it to display one row, with the latest payment date, and only SUM the Greetings once?


  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    script out the data for me please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Sorry - didn't see your original reply when I posted the second time. Your query pulls out exactly the right data - I am once again massively indebted to you. (And looking up the "Coalesce" reference page as I type...)

Posting Permissions

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