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

    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:

    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?

    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
    Toronto, Canada
    let me know if this works

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

  3. #3
    Join Date
    Jan 2002
    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
    Toronto, Canada
    script out the data for me please | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    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