Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

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
add comment

6 Answers 6

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
share|improve this answer
add comment
up vote 1 down vote accepted

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.

share|improve this answer
    
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
    
Awesome , Thanks It worked :-) –  Maddy Feb 4 '13 at 8:42
add comment
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
share|improve this answer
add comment

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
share|improve this answer
    
Perhaps this post may be answer to your question stackoverflow.com/questions/4711127/… –  Sumit Munot Feb 4 '13 at 7:13
add comment

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
share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.