I need to combine three tables, group the results by one column and display the largest value from another column

Question

Let's say I have three tables:

• users with identifiers of names
• emails containing strings that have their email address
• messages containing strings containing the dates and names of their letters

the second and third tables can be mapped to the first using a user ID.

I need a request that each user will return only once, his email address and the date of their last message.

If I use this:

 SELECT name,email,title,date FROM users JOIN emails ON users.id = emails.user_id JOIN messages ON messages.user_id = emails.user_id group by name order by date desc 

I do not receive the last message because the order occurred after joining and grouping. I receive one email from my users, and emails are sorted by date.

Can this be done in one connection? What am I missing?

Pastebin for a dummy database you can use: http://pastebin.com/1x273aEe - the actual database is not quite the same, but the same problem.

Answer 1

 SELECT a.*, b.*, c.* FROM users a INNER JOIN emails b ON a.ID = b.user_ID INNER JOIN messages c ON a.ID = c.user_ID INNER JOIN ( SELECT user_id, MAX(date) maxDate FROM messages GROUP BY user_ID ) d ON c.user_ID = d.user_ID AND c.date = d.maxDate 

• SQLFiddle Demo