CODEWITHSUNDEEP
mysql
momo
momo

Reputation: 3935

MySQL - GROUP_CONCAT with joins

My question is pretty similar to MySQL: is it possible to group_concat multiple rows?, but I can't get my head around how the group concat is working.

My actual dataset would be hard to explain - I'll use a simplified set of tables and relationships as an example.

say i have a table photos

id  filename
1   bob.jpg
2.  ken.png
3.  lisa.jpg
4.  jim.jpg
...

and a table tags

id  title
1   family
2   friends
3   school
4   california
...

and a bridge called photo_tags

photo  tag
1      2
1      3
1      4
2      1
3      1

and i want back rows like this:

photo_id  filename   tags
1         bob.jpg    2,3,4
2         ken.png    1
3         lisa.jpg   1
4         jim.jpg    (NULL)

or...

photo_id  filename   tags
1         bob.jpg    friends,school,california
2         ken.png    family
3         lisa.jpg   family
4         jim.jpg    (NULL)

My initial guess was something like this:

SELECT photos.filename,
GROUP_CONCAT(photo_tags.tag) AS associated_tags
FROM photos
LEFT OUTER JOIN tags
ON tags.photo = photos.id
GROUP BY photo_tags.tag

But (probably obvious to someone who knows what they're doing), that didn't work. After doing some research, and referencing the link above, I tried various permutations of this:

SELECT 
  file_name,
  GROUP_CONCAT(photo_tag_ids) AS associated_tags
FROM (
  SELECT 
    photos.filename AS file_name,
    photo_tags.tag AS photo_tag_ids
  FROM  
    photos
    INNER JOIN photo_tags
         ON photo_tags.photo= photos.id
) whatever
GROUP BY file_name
ORDER BY file_name;

TYIA.

Upvotes: 1

Views: 264

Answers (1)

juergen d
juergen d

Reputation: 204756

SELECT photos.filename,
       GROUP_CONCAT(photo_tags.tag) AS associated_tags
FROM photos
LEFT OUTER JOIN tags ON tags.photo = photos.id
GROUP BY photos.filename

You have to group by a column that will be unique for every row and in the group you can build a group_concat of other columns. So change your group by to photos.filename.

Upvotes: 4

Related Questions