Tricky MySQL Recursive Query
I have a table that has 2 columns usersID
and theirssiblingID
What is the best way to find all the siblings of a given user?
The problem is complex. Here's 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
source to share
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 which did not explicitly look at the ANSI specs, you need to remove the keyword recursive
(it is required by the standard)
source to share
The answer, even using more than one SQL statement, was much more complicated than I thought.
Simple answer to your question: create a table with all sister relationships. Then you can simply request this as:
select siblingid
from @allsiblings sa
where sa.userid = 3
One note. I use SQL Server syntax, simply because it is the most user-friendly database. I only use functionality in MySQL, so it's easy to translate.
How do I create the @AllSiblings table? Well, just keep adding sibling pairs that don't exist until they are gone. We get couples by doing our own pairing.
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;
source to share
http://sqlfiddle.com/#!4/0ef0c/5 is an example where someone had to get all the relatives of a certain record.
The related question is here: Hierarchical query requires to pull out children, parents and siblings
source to share