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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

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

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

Your Answer

 
discard

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

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