JK36 JK36 - 6 months ago 6
SQL Question

Output from my query not quite right, possibly subquery for this?

Need some help structuring my query. I think I need a subquery, but I am not quite sure how to use them in my context. I have the following tables and data,

people
ID, Name
1, David
2, Victoria
3, Brooklyn
4, Tom
5, Katie
6, Suri
7, Kim
8, North
9, Kanye
10,James
11,Grace


relationship
peopleID, Relationship, relatedID
3,Father,1
3,Mother,2
6,Father,4
6,Mother, 5
8,Mother,7
8,Mother,9
11,Father,10


I have the following query

SELECT DISTINCT p.ID, p.name, f.ID, f.name, m.ID, m.name
FROM people AS p
LEFT JOIN relationship AS fr ON p.ID = fr.peopleID
LEFT JOIN people AS f ON fr.relatedID = f.ID

LEFT JOIN relationship AS mr ON p.ID = mr.peopleID
LEFT JOIN people AS m ON mr.relatedID = m.ID
WHERE p.ID IN(3,6,8,11)
AND (
mr.Relationship IN('Mother','Stepmother')
OR fr.Relationship IN('Father','Stepfather')
)


The query above outputs the following data

3,Brooklyn,1,David,1,David
3,Brooklyn,1,David,2,Victoria
3,Brooklyn,2,Victoria,2,Victoria
6,Suri,4,Tom,4,Tom
6,Suri,4,Tom,5,Katie
6,Suri,5,Katie,5,Katie
8,North,7,Kim,7,Kim
8,North,9,Kanye,7,Kim
8,North,9,Kanye,9,Kanye
11,Grace,10,James,10,James


I kind of understand what is going on, hence the reason I am thinking I probably need a subquery or possibly a union to get the parents first and then build on those results. I am trying to output the following, can anyone help please?

3,Brooklyn,1,David,2,Victoria
6,Suri,4,Tom,5,Katie
8,North,9,Kanye,7,Kim
11,Grace,10,James,, <-should display no mother details (same for the father if father was not in the data)

Answer

Sorry I have no possibility to check a query right now. Does this work?

SELECT DISTINCT p.ID, p.name, f.ID, f.name, m.ID, m.name 
FROM people AS p 
LEFT JOIN relationship AS fr 
       ON p.ID = fr.peopleID
      AND fr.relationship IN ('Father','Stepfather')
LEFT JOIN people AS f
       ON fr.relatedID = f.ID
LEFT JOIN relationship AS mr
       ON p.ID = mr.peopleID
      AND mr.relationship IN('Mother','Stepmother')
LEFT JOIN people AS m 
       ON mr.relatedID = m.ID 
WHERE p.ID IN(3,6,8,11)

The point is to get rid of using (WHERE A OR B) together with LEFT JOIN. It brings too much uncertainty in result's logic

Comments