2

I have a database with users and notes related to each user in a one to many relationship.

I want to make a query which returns the details of the users including the last note which was made about them.

To illustrate here's an example of the data:

enter image description here

So for user David the note included in the results should be note.id=6 and for John note.id=3 for instance.

SELECT user.firstname, 
       user.lastname, 
       note.note 
LEFT JOIN note
ON note.userID = user.id

What can I add to this SQL query to only include the last added note for each user?

Arun
  • 941
  • 6
  • 13
dlofrodloh
  • 1,728
  • 3
  • 23
  • 44
  • 2
    This is something of a FAQ. Someone will probably be along with a (correct) answer in a minute, but it might be an idea to try searching for something similar – Strawberry Oct 02 '14 at 16:33
  • possible duplicate of [SQL join: selecting the last records in a one-to-many relationship](http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship) – Nickolay Oct 02 '14 at 19:28

2 Answers2

2

add a where clause, and select from the MAX(id) that is available.

WHERE note.id = ( SELECT MAX(id) FROM note)

EDIT:

SELECT user.firstname, 
       user.lastname, 
       note.notes 
FROM user
LEFT JOIN note ON note.userid = user.id
WHERE note.id = ( SELECT MAX(id) FROM note
                  WHERE note.userid = user.id)
OR note.userid IS NULL --EDIT
Arun
  • 941
  • 6
  • 13
  • I just tried that and it only returns users who have more than one note. Users with only one note or no notes in the database aren't returned. Do you know how I could fix that? Thanks – dlofrodloh Oct 02 '14 at 16:53
  • Try adding a where inside that. (...from note where note.userid = user.id) – Arun Oct 02 '14 at 16:55
  • @user2721465 It works, I just edited my post. Here is the Fiddle http://sqlfiddle.com/#!2/1e0e9/7/0 – Arun Oct 02 '14 at 17:56
  • Sorry, yes it works I was adding it in the wrong place. Many thanks – dlofrodloh Oct 02 '14 at 18:05
  • Ah, darn it... sorry, just double checked and it still omits rows where the user has no notes. I need it to also show users who don't have any notes. (in the fiddle if you remove the Tim note it doesn't output his data) – dlofrodloh Oct 02 '14 at 18:29
  • @user2721465 Can you please update the fiddle, and post a new link to let us know what you are seeing. I'm not able to replicate it. For me, I see null values, and I see values that are blank. – Arun Oct 02 '14 at 18:32
  • Any joy? In that fiddle I just removed the inserted note for Tim and he doesn't show up in the result – dlofrodloh Oct 02 '14 at 19:24
  • @user2721465 Just saw your comment, add this to the end of the query. `OR note.userid IS NULL`. Original post re-edited. – Arun Oct 02 '14 at 19:34
0
SELECT firstname, lastname, note
FROM user u
LEFT JOIN (SELECT *
           FROM note
           ORDER BY id DESC) n
ON u.id = n.userid
GROUP BY u.id
Fernando Martín Besteiro
  • 745
  • 5
  • 16
  • SELECTing columns not listed in the GROUP BY clause is invalid in standard SQL. While MySQL allows you to do that, "the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause" per http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html – Nickolay Oct 02 '14 at 19:12
  • Surely you don't mean this as an argument that the solution is correct? – Nickolay Oct 02 '14 at 21:46
  • No it doesn't, it returns a random `note` for the `user`, as the documentation says. That the random note happens to be the last note in this specific example doesn't mean it always will be. – Nickolay Oct 03 '14 at 10:48
  • http://sqlfiddle.com/#!2/0e6ae3/1 New test.. works as expected... Can you show me an example of that query returning wrong values please? Otherwise I can't seem to find the mistake. – Fernando Martín Besteiro Oct 03 '14 at 17:01
  • No I can't. But I assume the MySQL developers who wrote the page I referenced can. – Nickolay Oct 03 '14 at 18:21