dimanche 12 juin 2016

SQL - Return latest comment for every row [duplicate]

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