MySQL join two table if second table is not empty

Question

I have 2 tables like below

table 1: users

id   name 
1    John
2    Jane
3    Mery
4    Alpha

table 2 : notes

id   note         owner created
1    alpha alpha    1   2013-02-03 10:30
2    yeah yeah..    2   2013-02-03 10:35
3    crap crap..    2   2013-02-03 10:50
2    yeah yeah..    3   2013-02-03 10:50
3    blah blah..    3   2013-02-03 10:55

Now i want to show in a list all the users with their latest notes like below

id   name     note
1    John     alpha alpha
2    Jane     crap crap..
3    Mery     blah blah..
4    Alpha    

How can I get this in one query? (PS: I am using PHP for scripting)

Answer 1

SQLFiddle demo

select users.*, notes.note from users
LEFT JOIN
(
select owner,max(created) as mc from notes group by owner
) t on users.id=t.owner 
LEFT JOIN notes on t.owner=notes.owner and t.mc=notes.created

Answer 2

This should work:

SELECT U.Id, U.Name, N.Note
FROM Users U
   LEFT JOIN (SELECT Owner, Max(created) maxCreated
         FROM Notes
         GROUP BY Owner) MaxN ON U.Id = MaxN.Owner
   LEFT JOIN Notes N ON U.Id = N.Owner AND N.Created = MaxN.maxCreated

Good luck.

Answer 3

Use a combination of ORDER BY, LEFT JOIN and GROUP BY:

SELECT users.id, users.name, ordered_notes.note
FROM users
LEFT JOIN (
    SELECT owner, note
    FROM notes
    ORDER BY created DESC, id DESC
) AS ordered_notes ON users.id = ordered_notes.owner
GROUP BY users.id, users.name

It returns one result when two notes by same user have same date.

SQL Fiddle

Answer 4

SELECT  a.id, a.name, b.note, MAX (b.createdDate)
FROM    users a
        LEFT JOIN notes b
            ON a.iD = b.Owner
group by 1,2,3

Answer 5

if you want to get the latest created date for each owner.

SELECT  a.*, x.*
FROM    users a
        LEFT JOIN
        (
            SELECT  b.*
            FROM    notes b INNER JOIN
                    (
                        SELECT  owner, MAX(created) max_date
                        FROM    notes
                        GROUP   BY owner
                    ) c ON  b.owner = c.owner AND
                            b.created = c.max_date
        ) x ON a.ID = x.owner

• SQLFiddle Demo

Answer 6

try this also.

 SELECT users.id ,users.name,notes.created FROM users
    LEFT JOIN notes ON
    users.id=notes.owner
    inner JOIN
    (
                            SELECT  OWNER, MAX(created) max_date
                            FROM    notes
                            GROUP   BY OWNER) sq
    ON notes.created=sq.max_date
    and notes.owner=sq.OWNER