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)

migrated from stackoverflow.com Feb 7 '13 at 7:04

This question came from our site for professional and enthusiast programmers.

  • What does this question have to do with database "administration"? – Salman A Feb 7 '13 at 7:32
  • @SalmanA Please look at our FAQ and /About page. We are also about advanced SQL/querying, and database design among other things. – JNK Feb 7 '13 at 13:37

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.

  • Thanks, But What if the note is empty for a user? like in the example user 4? – Maddy Feb 4 '13 at 7:35
  • Add a LEFT JOIN instead -- I'll edit... – sgeddes Feb 4 '13 at 7:36

SQLFiddle demo

select users.*, notes.note from users
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
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

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

try this also.

 SELECT users.id ,users.name,notes.created FROM users
    LEFT JOIN notes ON
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.