crispychicken crispychicken - 18 days ago 6
SQL Question

How can I combine these 2 queries to one?

I have these two queries where I'm not able to combine them properly.

I want to get comments for a particular discussion and every comment can have multiple images which are stored in the images table.

SELECT c.CommentID, c.Body, u.Name
FROM Comments c
JOIN User u ON u.UserID = c.InsertUserID
WHERE c.DiscussionID = 1

SELECT Path
FROM Images
WHERE commentID = ?


This is what I came up with, but it's not working:

SELECT c.CommentID, c.Body, u.Name, i.Path
FROM Comments c
JOIN User u ON u.UserID = c.InsertUserID
LEFT JOIN Images i ON c.CommentID = i.ForeignID
WHERE c.DiscussionID = 1

Answer
SELECT c.CommentID, c.Body, u.Name, (SELECT GROUP_CONCAT(Path SEPARATOR "\n") FROM Images i WHERE i.commentID = c.CommentID) as 'images'
FROM Comments c
JOIN User u ON u.UserID = c.InsertUserID
WHERE c.DiscussionID = 1

You'll receive one row for each comment and all of comment images will be in 'images' field. Images will be separated by newline ("\n") character.