Scribd
Upload
24 views

Complex SQL Query Script Find Family Members

The document contains SQL commands to create and populate a table named 'family_members' with various family relationships. It also includes a recursive query to identify related family members and group them based on their relationships. The final output generates a list of family IDs and their corresponding relatives.

Uploaded by

shanumehta007
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
24 views

Complex SQL Query Script Find Family Members

The document contains SQL commands to create and populate a table named 'family_members' with various family relationships. It also includes a recursive query to identify related family members and group them based on their relationships. The final output generates a list of family IDs and their corresponding relatives.

Uploaded by

shanumehta007
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

drop table family_members;

create table family_members


(
person_id varchar(20),
relative_id1 varchar(20),
relative_id2 varchar(20)
);

insert into family_members values('ATR-1', null, null);


insert into family_members values('ATR-2', 'ATR-1', null);
insert into family_members values('ATR-3', 'ATR-2', null);
insert into family_members values('ATR-4', 'ATR-3', null);
insert into family_members values('ATR-5', 'ATR-4', null);

insert into family_members values('BTR-1', null, null);


insert into family_members values('BTR-2', null, 'BTR-1');
insert into family_members values('BTR-3', null, 'BTR-2');
insert into family_members values('BTR-4', null, 'BTR-3');
insert into family_members values('BTR-5', null, 'BTR-4');

insert into family_members values('CTR-1', null, 'CTR-3');


insert into family_members values('CTR-2', 'CTR-1', null);
insert into family_members values('CTR-3', null, null);

insert into family_members values('DTR-1', 'DTR-3', 'ETR-2');


insert into family_members values('DTR-2', null, null);
insert into family_members values('DTR-3', null, null);

insert into family_members values('ETR-1', null, 'DTR-2');


insert into family_members values('ETR-2', null, null);

insert into family_members values('FTR-1', null, null);


insert into family_members values('FTR-2', null, null);
insert into family_members values('FTR-3', null, null);

insert into family_members values('GTR-1', 'GTR-1', null);


insert into family_members values('GTR-2', 'GTR-1', null);
insert into family_members values('GTR-3', 'GTR-1', null);

insert into family_members values('HTR-1', 'GTR-1', null);


insert into family_members values('HTR-2', 'GTR-1', null);
insert into family_members values('HTR-3', 'GTR-1', null);

insert into family_members values('ITR-1', null, null);


insert into family_members values('ITR-2', 'ITR-3', 'ITR-1');
insert into family_members values('ITR-3', null, null);

select * from family_members;

-- Solution in PostgreSQL
with recursive related_fam_members as
(select * from base_query
union
select fam.person_id, r.family_group
from related_fam_members r
join family_members fam on fam.relative_id1 = r.relatives or
relative_id2 = r.relatives
),
base_query as
(select relative_id1 as relatives, substring(person_id,1,3) as
family_group
from family_members where relative_id1 is not null
union
select relative_id2 as relatives, substring(person_id,1,3) as
family_group
from family_members where relative_id2 is not null
order by 1),
no_relatives as
(select person_id
from family_members fam
where relative_id1 is null and relative_id2 is null
and person_id not in (select relatives from base_query))
select concat('F_', row_number() over(order by relatives)) as Family_id, relatives
from
(select distinct string_agg(relatives, ', ' order by relatives) as relatives
from related_fam_members
group by family_group
union
select * from no_relatives
) x;

You might also like