I have two tables. I need to join them, but I have a doubt since these two tables do not have a common column name.

people

relations

Desired output:

My approach:

select distinct 
    people.name as child, 
    people.name as father, people.name as mother
from
    relations 
inner join 
    people on relaions.c_id = people.id

This is the output of this query:

How to get the desired output in order of the name of the child?

CodePudding user response：

Join the people table twice, once for the parent, once for the child. Then group by child and use conditional aggregation to get father and mother.

select
  c.name as child,
  max(case when p.gender = 'M' then p.name end) as father,
  max(case when p.gender = 'F' then p.name end) as mother
from relations r
join people p on p.id = r.p_id
join people c on c.id = r.c_id
group by c.id, c.name
order by c.name;

And just to show that this is also possible of course without aggregation (and to show an approach that may come easier to mind when you are a beginner):

with fathers as
(
  select p.name, r.c_id
  from relations r
  join people p on p.id = r.p_id
  where p.gender = 'M'
)
, mothers as
(
  select p.name, r.c_id
  from relations r
  join people p on p.id = r.p_id
  where p.gender = 'F'
)
, children as
(
  select c.name, c.id as c_id
  from people c
  where c.id in (select r.c_id from relations r)
)
select
  children.name as child,
  fathers.name as father,
  mothers.name as mother
from children
left join mothers on mothers.c_id = children.c_id
left join fathers on fathers.c_id = children.c_id
order by children.name;

I am using outer joins here for the case that a child's mother or father is unknown (i.e. not in the relation table).

CodePudding user response：

Nested joins should work neatly. No CTEs or grouping necessary.

• Left join relations...
• ... and within that, inner join people who have gender F
• Repeat again for gender M
• Exclude any who already have children

select
  c.name as child,
  father.name as father,
  mother.name as mother
from people c
left join relations rm
    join people mother on mother.id = rm.p_id and mother.gender = 'F'
  on rm.c_id = c.id
left join relations rf
    join people father on father.id = rf.p_id and father.gender = 'M'
  on rf.c_id = c.id
where c.id not in (
  select r.p_id
  from relations r
);

db<>fiddle