Aaron Aaron - 4 months ago 8x
MySQL Question

Nested select statement in mysql

I have two tables a messages and a comments sections. A messages can have many comments, but a comments can only have one message. I am trying to write a sql select statement that would return the message and all comments referring to that message in one row. Is there a way to do that in mysql? How would I go about it. The comments has a message id which is a foreign key that relates to the id of messages. Here is my ERD diagram

enter image description here


You should use group_concat and for group_concat you need group by

SELECT message, GROUP_CONCAT(comment SEPARATOR ';') as  comments
FROM   messages 
JOIN   comments  ON messages .id = comments  .message_id
GROUP By message