I got a table people
:
ID name surname mother_id father_id -------------------------------------------- 1 John smith null null 2 kate m null null 3 philip smith 2 1 4 dimitr smith 7 3
etc.
I tried this query.
SELECT p.name, P.surname, c.name as 'Mothers name', c.surname as 'Mothers surname', gm.name as 'Grandmother name', gm.surname as 'Grandmother surname' FROM dbo.people AS p JOIN dbo.people AS c ON (c.mother = p.Id) JOIN dbo.people AS gm ON (gm.mother = c.Id)
But it doesn’t really return what I expected
Advertisement
Answer
Problem with ON
clause :
select p.name, p.surname, coalesce(pm.name, '') as mothername, coalesce(pm.surname, '') as mothersurname, coalesce(pf.name, '') as grandmothername, coalesce(pf.surname, '') as grandmothersurname from people p left join people pm on pm.id = p.mother_id left join -- use mother_id from people p people pf on pf.id = p.father_id; -- use father_id from people p;
Here is db-fiddle.