Brain has abandoned me - return max date for each unique pair of foreign keys

[directormac]

Happy whatever you celebrate to everyone! With the quiet holiday weeks I've been making long overdue progress on my EAV-based project, in fact I was hoping to post it for feedback soon, but I realized today I was making a simple mistake somewhere and apparently I've looked at it too many times to be able to solve it.

This isn't directly EAV-related, so here's a simplified version.

Say you're relating many-to-many by way of a joining table. Not even complicated. A list of diners and a list of restaurants and a joining table that records which diner visited which restaurant on what date:

Now suppose you want to see a list of all diners, with all the restaurants they've been to, but ONLY the MOST RECENT visit to any given restaurant. So you need only the most recent value of dtmVisitDate for all unique lngDinerID-lngRestaurantID pairs. Such that this list:

Would look nearly the same, but record #1 (Fred/Sonic/Jan 1) would be absent because Fred went to Sonic again on Nov 11.

I can get the right rows if I use a distinct select, but then I lose the autoDinersToRestaurantsKEY field:

SELECT lngDinerID, lngRestaurantID, MAX(dtmVisitDate) AS dtmMostRecentMeal
FROM tblDinersToRestaurants
GROUP BY lngDinerID, lngRestaurantID
ORDER BY lngDinerID, lngRestaurantID;

Or I can get all the fields but with duplicate rows. And, try as I might, I can't write a union off the first version that gives me the right results either. I know I'm forgetting something simple, I just don't know what it is. Obvs. Searching brings me plenty of results for getting what I'm already getting (latest date value but without bringing in non-grouped fields).

PLEASE NOTE: Because of my work situation, I CANNOT download files. I know that makes it a pain to help me, I apologize.

Example attached.

 

[plog]

If you need the autoDinnersToRestaurantKey you use the query that works in a subquery. Bring tblDinerstoRestuarant and the query you posted into a new query. JOIN them by all 3 fields, then bring down all fields from the query and the autoDinnersToRestuarantKey field.

 

[jdraw]

Is this the result you are expecting?

Query1 Query1

txtDinerName	lngDinerID	lngRestaurantID	txtRestaurantName	dtmMostRecentMeal
Fred	1​	1​	Sonic	11-Nov-21​
Fred	1​	2​	Pizza Hut	02-Jan-21​
Barney	2​	2​	Pizza Hut	02-Feb-21​
Betty	3​	3​	Quilted Giraffe	03-Mar-21​
Wilma	4​	4​	Shake Shack	04-Apr-21​

If so, this is the sql:

SELECT tblDiners.txtDinerName, tblDinersToRestaurants.lngDinerID
, tblDinersToRestaurants.lngRestaurantID, tblRestaurants.txtRestaurantName
, Max(tblDinersToRestaurants.dtmVisitDate) AS dtmMostRecentMeal
FROM tblRestaurants INNER JOIN (tblDiners INNER JOIN tblDinersToRestaurants
ON tblDiners.autoDinerKEY = tblDinersToRestaurants.lngDinerID)
ON tblRestaurants.autoRestaurantKEY = tblDinersToRestaurants.lngRestaurantID
GROUP BY tblDiners.txtDinerName, tblDinersToRestaurants.lngDinerID
, tblDinersToRestaurants.lngRestaurantID, tblRestaurants.txtRestaurantName
ORDER BY tblDinersToRestaurants.lngDinerID, tblDinersToRestaurants.lngRestaurantID;

 

[directormac]

If you need the autoDinnersToRestaurantKey you use the query that works in a subquery. Bring tblDinerstoRestuarant and the query you posted into a new query. JOIN them by all 3 fields, then bring down all fields from the query and the autoDinnersToRestuarantKey field.

My apologies, I didn't intend to post&ghost, something came up. I swear I did try this, but as I mentioned I had been at it too long and wasn't able to make it work.

 

[directormac]

Is this the result you are expecting?

If so, this is the sql:

YES! And all in one query. Thank you! Apologies for being slow, something came up right after I posted that had to be dealt with.

 

[arnelgp]

I can get the right rows if I use a distinct select, but then I lose the autoDinersToRestaurantsKEY field:

so you also need the above field?
create first query (query1):

SELECT tblDinersToRestaurants.lngDinerID,
tblDinersToRestaurants.lngRestaurantID,
Max(tblDinersToRestaurants.dtmVisitDate) AS MaxOfdtmVisitDate
FROM tblDinersToRestaurants
GROUP BY tblDinersToRestaurants.lngDinerID, tblDinersToRestaurants.lngRestaurantID;

next create the final query (query2) and join tblDinersToRestaurants again to query1.

SELECT tblDinersToRestaurants.autoDinersToRestaurantsKEY,
Query1.lngDinerID, Query1.lngRestaurantID,
Query1.MaxOfdtmVisitDate AS VisitDate
FROM Query1 INNER JOIN tblDinersToRestaurants
ON (Query1.MaxOfdtmVisitDate = tblDinersToRestaurants.dtmVisitDate)
   AND (Query1.lngRestaurantID = tblDinersToRestaurants.lngRestaurantID)
   AND (Query1.lngDinerID = tblDinersToRestaurants.lngDinerID)
ORDER BY Query1.lngDinerID, Query1.lngRestaurantID;

Query2 Query2

autoDinersToRestaurantsKEY	lngDinerID	lngRestaurantID	VisitDate
6​	1​	1​	11/11/2021​
5​	1​	2​	01/02/2021​
2​	2​	2​	02/02/2021​
3​	3​	3​	03/03/2021​
4​	4​	4​	04/04/2021​

 

[directormac]

so you also need the above field?
create first query (query1):

next create the final query (query2) and join tblDinersToRestaurants again to query1.

Thank you - that's what I was trying last night but wasn't able to put it together correctly.

Out of curiosity, what pros/cons do you see to the multi-query approach vs what jdraw suggested above? My # of users/records is unlikely to get so large that the performance difference will be noticeable, so I tend to prefer the single-query approach for tidiness.

 

[arnelgp]

it is best to build your Final query from smaller queries.
it is easier to maintain.
rather than looking at a complex single query when it
is time to troubleshoot.

whether you use 1 or multi queries is up to you.
queries are optimized so there are no significant gain
in speed when choosing one over the other.