Tricky Recursive MySQL query

Question

I have a table that has 2 column, usersID and their siblingID

What is the best way to find all the siblings of a given user?

The issue is complex. Here is an example.

User 1 has 5 siblings(2,3,4,5,6)

The table looks like this

userID|siblingID
1     | 2
1     | 3
6     | 5
5     | 3
3     | 1
4     | 6

What database are you using? I assume you are looking for a recursive solution, so 5, 3, and 1 are all siblings of 6. — Gordon Linoff, Jan 10 '13 at 20:31

score 3 · Accepted Answer · answered Jan 10 '13 at 20:46

3

ANSI SQL:

with recursive tree (userid, siblingid) as
(
   select userid, 
          siblingid
   from users
   where userId = 1
   union all 
   select c.userid,
          c.siblingid
   from users c
     join tree p on p.userid c.siblingId
)
select *
from tree;

For Oracle 11.2 and SQL Server - who apparently didn't look at the ANSI specs closely - you need to remove the recursive keyword (it is mandatory according to the standard)

answered Jan 10 '13 at 20:46

Thanks for the help! I forgot to specify that i am using MySQL. This does not seem to work for MySQL – FredTheLover Jan 10 '13 at 21:10
@Jakobovski: correct. MySQL does not support any of the modern SQL features like recursive queries, windowing functions and so on. – Jan 10 '13 at 21:11
Is there anyway to do this with MySQL or do I need to do it outside of the database in PHP? – FredTheLover Jan 10 '13 at 21:13
@Jakobovski: with MySQL it's best to do it using PHP. – Jan 10 '13 at 21:14
How does this handle cycles? – Gordon Linoff Jan 10 '13 at 22:00
@GordonLinoff: it doesn't (as it is). But it's possible to add a cycle check e.g. by assembling the path to a sibling and checking that in the join condition (which would then in turn terminate the recursion). – Jan 10 '13 at 22:13

score 1 · Answer 2 · answered Jan 10 '13 at 22:22

The answer, even using more than one SQL statement, was much harder than I thought it would be.

The easy answer to your question is: create a table with all sibling relationships. Then you can just query this as:

select siblingid
from @allsiblings sa
where sa.userid = 3

One note. I'm using the syntax of SQL Server, just because that happens to be the most handy database. I'm only using functionality in MySQL so it should be easy to translate.

How to create the table @AllSiblings? Well, just keep on adding sibling pairs in that don't exist, until there are no more to add. We get the pairs by doing a self join.

Here is the code (subject to the previous caveat):

declare @allsiblings table (userid integer, siblingid integer);

declare @siblings table (userId int, siblingID int);

-- Initialize the @siblings table    
insert into @siblings(userId, siblingID)
    select 1 as userID, 2 as siblingID union all
    select 1 as userID, 3 as siblingID union all
    select 6 as userID, 5 as siblingID union all
    select 5 as userID, 3 as siblingID union all
    select 3 as userID, 1 as siblingID union all
    select 4 as userID, 6 as siblingID;

-- Initialize all siblings.  Note that both pairs are going in here    
insert into @allsiblings(userid, siblingid)
    select userId, siblingid from @siblings union
    select siblingID, userid from @siblings

-- select * from @allsiblings

while (1=1)
begin
    -- Add in new siblings, that don't exist by doing a self-join to traverse the links
    insert into @allsiblings
        select distinct sa.userid, sa2.siblingid
        from @allsiblings sa join
             @allsiblings sa2
             on sa.siblingid = sa2.userid
        where not exists (select * from @allsiblings sa3 where sa3.userid = sa.userid and sa3.siblingid = sa2.siblingid)

    -- If nothing was added, we are done        
    if (@@ROWCOUNT = 0) break;

    select * from @allsiblings;
end;

score 0 · Answer 3 · answered Jan 10 '13 at 20:38

0

you can simulate recursion with a loop and a temp table. first insert in the temp table the start node. then while there are rows in the temp table, get the first one, delete it from the temp table and in it's place insert all his siblings...

answered Jan 10 '13 at 20:38

Z .

12,657
1
31
56

score 0 · Answer 4 · edited May 23 '17 at 11:43

0

http://sqlfiddle.com/#!4/0ef0c/5 is an example where someone had to get all the relatives of certain entries.

The corresponding stack overflow question is here: Hierarchical Query Needs to Pull Children, Parents and Siblings

edited May 23 '17 at 11:43

Community

1
1

answered Jan 10 '13 at 20:41

Fenster

340
3
9

Tricky Recursive MySQL query

4 Answers4

Linked