shabir ullah shabir ullah - 2 months ago 10
MySQL Question

Joins - get row from one table then get all rows that match in another table? but first table matching row should not be repeated

I have two tables. Gallery and Joins.

Gallery
id | title
1 | Dog Gallery
2 | Cat Gallery

Joins
id | gallery_id | picture_id
1 | 1 | 100
2 | 1 | 101
3 | 2 | 56
4 | 1 | 102


I want to get id, gallery title from gallery - where id is equal to a specific id but also get all of the rows from joins where gallery id equals a specific gallery id.

So for above if the id was 1. I would want the dog gallery from gallery and picture_ids 100,101 and 102 from joins.

now the problem is that gallery title should comes one time.
result is title=dog gallery picture_id=100,title=dog gallery picture_id=101,title=dog gallery picture_id=102 i need the result like this
title=dog gallery picture_id=100,picture_id=101 and picture_id=102

Answer

You can use GROUP_CONCAT to have all the IDs as string

SELECT G.*, GROUP_CONCAT(DISTINCT P.picture_id ORDER BY P.picture_id DESC SEPARATOR ',') 
FROM gallery as G
LEFT JOIN pictures as P ON P.gallery_id = G.id 
GROUP BY G.id;
Comments