mmmoustache mmmoustache - 19 days ago 8
MySQL Question

MySQL - select results from a secondary table as a column in a primary table

I have a set of data consisting of two tables: table one is a set of unique items, and table two is a log of references that link the items in the first table together. For example:

Table one

+------------+--------------------+
| id | name |
+------------+--------------------+
| 1 | Item 1 |
| 2 | Item 2 |
| 3 | Item 3 |
| 4 | Item 4 |
| 5 | Item 5 |
+------------+--------------------+


Table two

+------------+--------------------+
| item_1_id | item_2_id |
+------------+--------------------+
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 4 |
| 2 | 5 |
+------------+--------------------+


Is it possible to group the rows that are in table two, and display them in some sort of array/collection as a column in table one. So based on my example tables, I would hope to return something like this:

+------------+-----------+----------+
| id | name | results |
+------------+-----------+----------+
| 1 | Item 1 | 2, 3, 5 |
| 2 | Item 2 | 1, 4, 5 |
| 3 | Item 3 | 1 |
| 4 | Item 4 | 2 |
| 5 | Item 5 | 1, 2 |
+------------+-----------+----------+

Answer

You can use group_concat for show the aggregated result and a select union for obtain both the related item for grouping

  select id, name, group_concat( item1)
  from table_one 
  left join 
  (select  item_1_id as item1, item_2_id  as item2  
  from table_two
  union  
  select  item_2_id , item_1_id    
  from table_two
  order by item1)  t1 ont1.item1 = table_one.id
  group by id, name