Nested select statement in mysql

Question

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

ScaisEdge · Accepted Answer · 2016-06-12 20:18:27Z

1

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

answered Jun 12, 2016 at 20:18

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2016-06-12 20:35:51Z

1

You could use the group_concat function:

SELECT   message, GROUP_CONCAT(comment SEPARATOR ';') AS all_comments
FROM     messages m
JOIN     comments c ON m.id = c.message_id
GROUP BY m.id, message

edited Jun 12, 2016 at 20:35

answered Jun 12, 2016 at 20:07

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Collectives™ on Stack Overflow

Nested select statement in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related