MySQL concatenate values from one table into a record of another

mysql

Solution

You can use the MySQL `GROUP_CONCAT()`:

select i.id,
  i.name,
  group_concat(t.name SEPARATOR ', ') tags
from items i
left join items_to_tags it
  on i.id = it.item_id
left join tags t
  on it.tag_id = t.id
group by i.id, i.name

See SQL Fiddle with Demo

Result:

| ID |  NAME |             TAGS |
---------------------------------
|  1 | item1 | tag1, tag2, tag3 |
|  2 | item2 |             tag3 |

Problem

I have three tables (many to many relationship): `items`, `items_to_tags`, and `tags`. The `items` and `tags` tables have a unique ID column, and the `items_to_tags` table has columns `item_id` and `tag_id`. Is there a way to select all results from the `items` and `tags` tables, but with all results merged into the same record? For instance, if I have this data: `items`: ``` id name 1 'item1' 2 'item2' ``` `tags`: ``` id name 1 'tag1' 2 'tag2' 3 'tag3' ``` `items_to_tags`: ``` item_id tag_id 1 1 1 2 1 3 2 3 ``` The result of the query should be: ``` item_id item_name tags 1 'item1' 'tag1,tag2,tag3' ```

Original source