Reputation: 5908
I have the following structure:
Attended:
id
user
date
Meetings:
id
data
place
Places
id
name
users
id
name
I'm trying to get the maximum date the user attended the meeting and i'm able to do this by running the following:
select (meetings.date), places.name, meetings.id, attend.user
from attend
inner join meetings on meetings.Id = attend.meeting
inner join places on meetings.place = places.id
WHERE meetings.date IN (select max(meetings.date) from meetings)
However, only the date will be correct, if i try to print out the name of the place it will not be the place that is associated with the meeting with the max date but it actually prints the place with first row that i get from the attend table.
What is the best approach to finding the max attended date based on the meeting date?
Upvotes: 0
Views: 108
Reputation: 92845
Are you looking for something like this?
SELECT a.user_name, a.meeting, a.place_name, a.date
FROM
(
SELECT a.user, a.meeting, m.date, p.name place_name, u.name user_name
FROM attended a JOIN meetings m
ON a.meeting = m.id JOIN places p
ON m.place = p.id JOIN users u
ON a.user = u.id
) a JOIN
(
SELECT a.user, MAX(m.date) date
FROM attended a JOIN meetings m
ON a.meeting = m.id
GROUP BY user
) q
ON a.user = q.user
AND a.date = q.date
ORDER BY user_name
Sample output:
| USER_NAME | MEETING | PLACE_NAME | DATE | -------------------------------------------------------------------- | User 1 | 2 | Place 2 | August, 11 2013 00:00:00+0000 | | User 2 | 1 | Place 1 | July, 12 2013 00:00:00+0000 | | User 3 | 1 | Place 1 | July, 12 2013 00:00:00+0000 |
Here is SQLFiddle demo
Upvotes: 1