CODEWITHSUNDEEP
mysqlsql
FredTheLover
FredTheLover

Reputation: 1019

Tricky Recursive MySQL query

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

Upvotes: 1

Views: 811

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

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;

Upvotes: 1

user330315
user330315

Reputation:

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)

Upvotes: 3

Fenster
Fenster

Reputation: 340

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

Upvotes: 0

Z .
Z .

Reputation: 12837

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

Upvotes: 0

Related Questions