CODEWITHSUNDEEP
sqlsql-server
uberxeq
uberxeq

Reputation: 43

Query to get grandparents and grandkids

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

Upvotes: 0

Views: 71

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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.

Upvotes: 1

Related Questions