SQL query question
Hopefully the solution to this is less complicated than it sounds - its just difficult to summarise without writing it down.
I have a main table, Lodges, and the related table Lodge_Updates. There are two other tables related to the main Lodges table - Countries and Lodge_Likes. Lodge_Likes just has a couple of fields - LodgeIDPL and UserPL - it narrows down the list to a list of Lodges that a particular user has liked.
And from that I would like to return a list of lodges that includes the Date_Updated field from the Lodge_Updates table, where that is the date of the most recent update.
So for example, if I have these records in my Lodges table:
LodgeID, Lodge
1, Lodge A
2, Lodge B
3, Lodge C
And these records in my product updates table:
UpdateID, LodgeID, Update, Date_Updated
1, 1, Update A, 18 April 2015
2, 1, Update B, 1 April 2015
3, 2, Update C, 5 April 2015
4, 2, Update D, 15 April 2015
5, 3, Update E, 10 April 2015
6, 3, Update F, 30 April 2015
I would like to return the following list:
LodgeID, Lodge, Date_Updated
3, Lodge C, 30 April 2015
1, Lodge A, 18 April 2015
2, Lodge B, 15 April 2015
So I nearly have it with:
SELECT DISTINCT UpdateID, Lodges.LodgeID, Lodge, Update, Date_Updated
FROM Lodges
INNER JOIN Countries ON Lodges.CountryID = Countries.CountryID
INNER JOIN Lodge_Likes ON Lodges.LodgeID = Lodge_Likes.LodgeIDPL
LEFT JOIN Lodge_Updates ON Lodges.LodgeID = Lodge_Updates.LodgeID
WHERE Lodge_Likes.UserIDPL = 123
GROUP BY LodgeID
ORDER BY Date_Updated DESC
But that gives me the list based on the oldest update for any lodge date, not the most recent:
UpdateID, LodgeID, Lodge, Date_Updated
5, 3, Lodge C, 10 April 2015
3, 2, Lodge B, 5 April 2015
2, 1, Lodge A, 1 April 2015
If I change the ORDER BY to:
ORDER BY Date_Updated ASC
That just reverses that order:
UpdateID, LodgeID, Lodge, Date_Updated
2, 1, Lodge A, 1 April 2015
3, 2, Lodge B, 5 April 2015
5, 3, Lodge C, 10 April 2015
Hope that makes sense and is possible somehow.
Thanks.
