SQL to return all related rows in a single column

mysql, relational-database, sql

Solution

Please try this query:

select
    itemID1, group_concat(cast(itemID2 as char) separator ',')
from
(
    select itemID1, itemID2 from st where itemID1 = :ID
    union 
    select itemID2, itemID1 from st where itemID2 = :ID
    union
    select s1.itemID2, s2.itemID2 from st as s1 inner join st as s2 on s1.itemID1 = s2.itemID1
    where s1.itemID2 = :ID
    union
    select s1.itemID1, s2.itemID1 from st as s1 inner join st as s2 on s1.itemID2 = s2.itemID2
    where s1.itemID1 = :ID
) as subquery
where itemID1 <> itemID2
group by itemID1

This way you select relation in both ways (`union` provides distinctiveness) as well as relation between joined items (also in both ways).

Problem

I've seen references to recursion in SQL Server, but I'm using MySQL and require the result to be in a single column. If I have a table of relationships: ``` itemID1 | itemiD2 --------------- 1 | 2 1 | 3 4 | 5 ``` How do I select all IDs related to a single ID in either column? For example: 1 ==> 2,3 3 ==> 1,2 I tried self joins, but can't get all related IDs in a single column. If there's a better schema for this it's not too late to change the table. Thank you.

Original source