Geek Answers Handbook

SQL to return all related rows in a single column

I saw recursion links in SQL Server, but I use MySQL and require the result to be in the same column. If I have a relationship table:

itemID1 | itemiD2 --------------- 1 | 2 1 | 3 4 | 5

How to select all identifiers associated with one identifier in any column? For instance:

1 ==> 2,3

3 ==> 1.2

I tried to connect on my own, but cannot get all the associated identifiers in one column. If there is a more efficient scheme for this, it is not too late to change the table.

Thanks.

+4
source share
2 answers

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

Thus, you choose the relationship in both directions ( union provides a distinctive feature), as well as the ratio between the combined elements (also in both directions).

+5
source

Partial answer to the question. this is not about recursion, but rather 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
0
source

More articles:


All Articles