This question already has an answer here:
So i have two tables, one has all the data and other has a list of comments and I need to load the latest comment for each row in the data table. Here's how it looks like
Table: queries
id | name |
--------------------
1 | John Doe |
2 | Jane Doe |
3 | Alex Smith |
Table: messages
id | queriesId | comment | dateAdded |
--------------------------------------------------------
1 | 1 | Comment 1 | 2016-06-12 15:00:08 |
2 | 1 | Commetn 2 | 2016-06-12 15:01:08 |
3 | 2 | Comment 3 | 2016-06-12 15:05:35 |
4 | 2 | Comment 4 | 2016-06-12 15:06:35 |
I want the results to look like this
id | name | comment | dateAdded |
--------------------------------------------------------
1 | John Doe | Commetn 2 | 2016-06-12 15:01:08 |
2 | Jane Doe | Comment 4 | 2016-06-12 15:06:35 |
3 | Alex Smith | Null | Null |
I tried this code
SELECT
a.*,
b.comment,
b.LastComment
FROM
queries a
LEFT JOIN
(
SELECT
`comment`,
MAX(dateAdded) LastComment,
queriesid
FROM
messages
GROUP BY
queriesid
) b ON a.id = b.queriesid
GROUP BY
a.id
but the result I get is this
id | name | comment | dateAdded |
--------------------------------------------------------
1 | John Doe | Commetn 1 | 2016-06-12 15:01:08 |
2 | Jane Doe | Comment 3 | 2016-06-12 15:06:35 |
3 | Alex Smith | Null | Null |
So the only problem is comment is the first entry and not the one corresponding with the datetime column value. I've tried many alterations but it doesn't seems to be working. I'm sure it's a tiny tweak away from working but I can't seem to find it.
Any ideas?
Aucun commentaire:
Enregistrer un commentaire