One to Many query question

[Al Kramer]

Hello Friends

With the help of many of you here on the Accessworld forums I have created a VERY simple membership app for a local non-profit I volunteer for. (I know this sentence ends in a preposition, but I was afraid 'for which I volunteer' would sound a bit stodgy.) Grammar aside, I have a demographics table related to a payments table. In an effort to clean up legacy data it would be helpful to know THE LAST payment made for each member.

Relevent fields in demographics: [Lname], [Fname] [Id] (parent table)
Relevent fields in payment : [GiftDate], [FkDemoId] (child table) FkDemoId is the foreign key

I don't know how to structure a query to yeild this data.

Thanx in advance

Al

 

[DHookom]

To get the most recent payment for each payer your query would be a totals query grouping on FkDemoID and returning the Max of the GiftDate. This can be combined with the demographics table if you need additional data.

 

[cheekybuddha]

SELECT
  d.Fname & ' ' & d.Lname,
  p.LatestDonationDate
FROM (
  SELECT
    MAX(GiftDate) AS LatestDonationDate,
    FKDemoId
  FROM payment
  GROUP BY
    FKDemoId
) p
INNER JOIN demographics d
        ON p.FKDemoId = d.Id
ORDER BY
  d.Lname,
  d.Fname
;

 

[Al Kramer]

SELECT
  d.Fname & ' ' & d.Lname,
  p.LatestDonationDate
FROM (
  SELECT
    MAX(GiftDate) AS LatestDonationDate,
    FKDemoId
  FROM payment
  GROUP BY
    FKDemoId
) p
INNER JOIN demographics d
        ON p.FKDemoId = d.Id
ORDER BY
  d.Lname,
  d.Fname
;

Cheeky, this worked perfectly. Thank you so much.

 

[Al Kramer]

To get the most recent payment for each payer your query would be a totals query grouping on FkDemoID and returning the Max of the GiftDate. This can be combined with the demographics table if you need additional data.

Thanx D, looking at Cheeky's SQL through the lens of your reponse is really helping me a grip on SQL.

 

[DHookom]

Using a subquery seems overly complex when a simpler SQL statement should meet the stated requirements.

 

[cheekybuddha]

Using a subquery seems overly complex when a simpler SQL statement should meet the stated requirements.

Perhaps, but it avoids the risk of GROUP'ing on unindexed fields (eg if d.Fname or d.Lname aren't indexed), or weird results if there is more than one donor with the same Lname/Fname combination.

However, you're totally correct, it's arguably simpler to write something like:

SELECT
  d.Id,
  d.Fname,
  d.Lname,
  MAX(p.GiftDate) AS LatestDonationDate
FROM demographics d
INNER JOIN payment p
        ON p.FKDemoId = d.Id
GROUP BY
  d.Id,
  d.Fname,
  d.Lname
ORDER BY
  d.Lname,
  d.Fname
;

 

[DHookom]

Thanks for the more digestible SQL. Having the first and last names the same shouldn’t be an issue when including the ID field.

I doubt performance will be an issue with this application but it is good to understand the advantages of the subquery method.

 

[cheekybuddha]

shouldn’t be an issue when including the ID field.

Yes, I included that deliberately because often you see folk just grouping on the 'visible' portions of the joined table only.