Hi folks, I got a question from my junior in my organization-
Input the name of a person born in Generation
X and output the
names of all of their descendants born in Generation Z, but only
if these descendants are brother and sister (have the same parent)
/*
Create table Test (id int ,name
varchar(100),Parent_ID int null,Generation varchar(1),Gender varchar(6))
Insert into test
(id,name,Parent_ID,Generation,Gender)
select 1,'John',null,'x','male'
union
select 2,'Jill',1,'y','Female'
union
select 3,'Andy',2,'z','male'
union
select 4,'Ralph',2,'z','male'
union
select 5,'Lisa',null,'x','Female'
union
select 6,'Steve',5,'y','male'
union
select 7,'Sean',6,'y','male'
union
select 8,'Sarah',6,'y','Female'
union
select 9,'Emily',7,'z','Female'
union
select 10,'Matt',7,'z','male'
Drop table test
*/
select * from Test
If input is lisa
Then output should be:
-------------
Answer :
Declare @name varchar(50)='Lisa'
;with cte
as
(
Select id,name,Parent_id,generation,gender from test where name =@name and generation='x'
Union All
Select t.id, t.name,t.Parent_id,t.generation,t.gender from test t
join cte c on c. id=t.parent_id
)
,condition
as
(
select Parent_id,COUNT(*) cnt
from cte Where
generation='z' Group by parent_id having COUNT(*)>1
)
,sibling
as
(
select parent_id,Count(gender) cntg from (select distinct c.parent_id,c.gender from cte c join
condition co on co.parent_id=c.parent_id) a group by parent_id having count(gender)>1
)
select c.name from cte c join
sibling s on c.parent_id=s.parent_id
