Tricky SQL Question | SQL Intermediate Question 18

This is a tricky sql question where we need to map the children with their respective parents.
ddl commands :-
create table people(id int primary key not null,
name varchar(20),
gender char(2));
create table relations(c_id int,
p_id int,
FOREIGN KEY (c_id) REFERENCES people(id),
foreign key (p_id) references people(id)
);
insert into people (id, name, gender) values
(107,'Days','F'),
(145,'Hawbaker','M'),
(155,'Hansel','F'),
(202,'Blackston','M'),
(227,'Criss','F'),
(278,'Keffer','M'),
(305,'Canty','M'),
(329,'Mozingo','M'),
(425,'Nolf','M'),
(534,'Waugh','M'),
(586,'Tong','M'),
(618,'Dimartino','M'),
(747,'Beane','M'),
(878,'Chatmon','F'),
(904,'Hansard','F');
insert into relations(c_id, p_id) values
(145, 202),
(145, 107),
(278,305),
(278,155),
(329, 425),
(329,227),
(534,586),
(534,878),
(618,747),
(618,904);
select * from people;
select * from relations
#amazon #sqldeveloper #dataanalytics #flipkart #ai #meta #meesho #dataengineering

[@krishna369-t4o]

I used self join:
select p.name,
max(case when pp.gender = 'F' then pp.name end) Mother,
max(case when pp.gender = 'M' then pp.name end) Father
from people p
join relations r on p.id = r.c_id
join people pp on pp.id = r.p_id
group by p.name
Thanks for the video.

[@vijaygupta7059]

my solution in mssql DB :
with child as
(select p1.*,r1.p_id from people as p1 inner join relations as r1 on p1.id=r1.c_id
union
select p1.*,r1.p_id from people as p1 inner join relations as r1 on p1.id=r1.c_id
), main as
(
Select child.name as child_name ,people.name as perent_name , people.gender from child inner join people on child.p_id=people.id
)
Select child_name
,max(case when gender ='F' then perent_name else null end) as father
,max(case when gender ='M' then perent_name else null end) as mother
from main
group by child_name

[@florincopaci6821]

My approach:
WITH FLO AS (
SELECT P.NAME AS Child ,
(SELECT NAME FROM PEOPLE WHERE ID=R.p_id AND GENDER='M')AS 'Father',
(SELECT NAME FROM PEOPLE WHERE ID=R.p_id AND GENDER='F')AS 'Mother'
FROM PEOPLE P JOIN RELATIONS R ON P.id=R.c_id
)
SELECT Child, MAX(Mother)AS Mother, MAX(Father)AS Father
FROM FLO
GROUP BY CHILD;
Hope it helps.

[@lekshmij317]

Great content 👏 I have approached this in 2 ways, please share your comments
1. Using 2 CTEs, one having Child name, id & gender of child and second CTE is for holding Parent-Child info
WITH CHILD AS (
SELECT c_id, p_id, p.name as Child_Name, gender as Child_Gender
from people p
inner join relations r
on id = c_id
),
Relation AS (
SELECT Child_Name,
MAX(CASE WHEN p.gender = 'F' Then p.name END) Mother,
MAX(CASE WHEN p.gender = 'M' Then p.name END) Father
FROM people p
inner join CHILD c
on p.id = c.p_id
GROUP BY Child_Name
)
select * from Relation;
2. Using 3 CTEs, one additional CTE with both child & parent info
WITH CHILD AS (
SELECT c_id, p_id, p.name as Child_Name, gender as Child_Gender
from people p
inner join relations r
on id = c_id),
PARENT_CHILD AS (
SELECT C.c_id AS CHILD_ID, Child_Name, id AS PARENT_ID, p.name as Parent_Name, p.gender as Parent_Gender
FROM people p
inner join CHILD c
on p.id = c.p_id),
FINAL AS (
SELECT Child_Name,
MAX(CASE WHEN Parent_Gender = 'F' Then Parent_Name END) Mother,
MAX(CASE WHEN Parent_Gender = 'M' Then Parent_Name END) Father
FROM PARENT_CHILD
GROUP BY Child_Name)
select * from FINAL;

[@Code-Con]

Great share

[@Savenature635]

my approach in mysql :
with fathers as (select p.id,p.name as father_name,r.c_id
from people p
inner join relations r
on p.id=r.p_id
where p.gender='M'),
mothers as (
select p.id,p.name as mother_name,r.c_id
from people p
inner join relations r
on p.id=r.p_id
where p.gender='F')
select mother_name,father_name,pl.name as child_name
from mothers m
join fathers f
on m.c_id=f.c_id
join people pl
on f.c_id=pl.id
order by pl.name;

[@chandanpatra1053]

bro first explain it in excel with logic/steps . then convert each steps/logic to sql query. So that anyone can understand. But this time you have not explained it in simplified mannner. That " how to approach " punch is missing in your solution. Please reupload again with a very simplified way approach.

[@Code-Con]

sure will do that changes

[@warrior9117]

You have not explained properly why you have used max function

[@Code-Con]

it is mainly used to group the child id to skip the redundant data

[@karthikeyan9592]

You could use distinct right?

[@krishna369-t4o]

@@karthikeyan9592 I tried distinct but it didn't work.

[@blse2000]

SELECT CHILD.name AS [NAME]
,MAX(CASE WHEN MOTHER.GENDER = 'F' THEN MOTHER.NAME ELSE '' END) AS MOTHER
,MAX(CASE WHEN FATHER.GENDER = 'M' THEN FATHER.NAME ELSE '' END) AS FATHER

FROM PEOPLE CHILD
INNER JOIN RELATIONS R
ON R.C_ID = CHILD.ID
INNER JOIN PEOPLE FATHER
ON R.P_ID = FATHER.ID
INNER JOIN PEOPLE MOTHER
ON R.P_ID = MOTHER.ID
GROUP BY CHILD.name

[@arupchandra8602]

select p1.name as name
,max(case when p2.gender='F' then p2.name end) Mother
,max(case when p2.gender='M' then p2.name end) Father
from relations r
inner join people p1 on p1.id=r.c_id
inner join people p2 on p2.id=r.p_id
group by p1.name;