0

Im trying to get all the posts and all of their images in order of index separated by a comma. So the idx could be 0,1, or 2 meaning a post can have up to three pictures.

My tables are Posts and Post_Pictures

Post

postId: (PRIMARY KEY)
fullName: (VARCHAR)

Post_Pictures

postPictureId (PRIMARY KEY)
image (STRING)
idx (INT)
post_id (FOREIGN KEY)

The final query should look like this for images belonging to the same postId:

postId | fullName |            imagesInOrder         | 

   1       Sam       (Img Url), (Img Url), (Img Url)

This is my current query which returns only ONE post instead of all of them and puts every image concatenated into one column instead of only the images that belong to that post:

SELECT p.*, GROUP_CONCAT(pp.image SEPARATOR ',') as photos FROM Posts as p LEFT JOIN Post_Pictures as pp ON p.postId = pp.post_id;

samuscarella
  • 89
  • 2
  • 9
  • Use `GROUP_CONCAT()` to create the comma-separated list. – Barmar Feb 09 '18 at 01:11
  • Im looking for the full query. My expertise is not in SQL – samuscarella Feb 09 '18 at 01:12
  • StackOverflow expects you to [try to solve your own problem first](http://meta.stackoverflow.com/questions/261592), and we also [don't answer homework questions](https://softwareengineering.meta.stackexchange.com/questions/6166) (ignore if you're not asking about hw). Please update your question to show what you have already tried in a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve). For further information, please see [how to ask good questions](http://stackoverflow.com/help/how-to-ask), and take the [tour of the site](http://stackoverflow.com/tour) :) – Barmar Feb 09 '18 at 01:13

0 Answers0