CODEWITHSUNDEEP
mysqlsqlrelational-database
Ian Levy
Ian Levy

Reputation: 65

SQL to return all related rows in a single column

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.

Upvotes: 4

Views: 2382

Answers (2)

Kuba Wyrostek
Kuba Wyrostek

Reputation: 6221

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).

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269623

A partial answer to the question. this doesn't address the recursion but rather the transitivity.

select itemID1, itemID2
from ((select itemID1, itemID2
       from t
      ) union all
      (select itemID2, itemID1
       from t
      )
     ) t
group by itemID1, itemID2

To get them as a list:

select itemID1, group_concat(distinct cast(itemID2 as varchar(32)) separator ',')
from ((select itemID1, itemID2
       from t
      ) union all
      (select itemID2, itemID1
       from t
      )
     ) t
group by itemID1, itemID2

Upvotes: 0

Related Questions