CODEWITHSUNDEEP
mysqlmax
Dani
Dani

Reputation: 5908

Mysql Selecting Max with Multiple Joins

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

Answers (1)

peterm
peterm

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

Related Questions