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

Question

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)

Are you using SQL Server ? – Mincong Huang May 20 '16 at 11:36 — Mincong Huang, May 20 '16 at 11:36

score 0 · Accepted Answer · answered May 20 '16 at 11:45

0

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

answered May 20 '16 at 11:45

NLink

485
2
5

That seems to work from initial tests, didn't realise you could use an IN filter in the join itself. Always thought the IN had to go in the WHERE clause. – JK36 May 20 '16 at 11:53
A IN ('B', 'C' )has no difference from (A = 'B' OR A = 'C'), if that matters. Left join will always bring you something - and will it be NULL or actual values depends on join filter. BTW if you want to filter people with no parents present you'll have to use (..) WHERE (f.name IS NOT NULL or m.name IS NOT NULL) - in such cases where clause is impossible to replace – NLink May 20 '16 at 11:56
select cast(p.id as nvarchar(max)) + ',' + p.name + ( SELECT cast(p1.id as varchar(max)) + ',' + p1.name FROM relationship r1 inner join people p1 on r1.relatedID = p1.ID where r1.peopleID = p.id for xml path('') ) from relationship r inner join people p on p.ID = r.peopleID group by p.ID,p.name – Sanjay May 20 '16 at 12:02

Sanjay · Answer 2 · 2016-05-20T12:05:16.773

0

is this what you want ??

SELECT p.ID, p.Name, p1.ID, p1.Name
FROM relationship r
    INNER JOIN people p ON p.ID = r.peopleID
    INNER JOIN people p1 ON p1.ID = r.relatedID

select cast(p.id as nvarchar(max)) + ',' + p.name + ( SELECT cast(p1.id as varchar(max)) + ',' + p1.name FROM relationship r1 inner join people p1 on r1.relatedID = p1.ID where r1.peopleID = p.id for xml path('') ) from relationship r inner join people p on p.ID = r.peopleID group by p.ID,p.name

let me know if you want more.

edited May 20 '16 at 12:05

answered May 20 '16 at 11:49

Sanjay

342
2
9

select cast(p.id as nvarchar(max)) + ',' + p.name + ( SELECT cast(p1.id as varchar(max)) + ',' + p1.name FROM relationship r1 inner join people p1 on r1.relatedID = p1.ID where r1.peopleID = p.id for xml path('') ) from relationship r inner join people p on p.ID = r.peopleID group by p.ID,p.name – Sanjay May 20 '16 at 12:01

score 0 · Answer 3 · edited May 23 '17 at 12:23

Even though you've already accepted an answer, but I still want to provide the mine :

WITH familly AS
(
    SELECT
         child.ID AS childID
        ,child.Name AS childName
        ,Relationship AS relationship
        ,parent.ID AS parentID
        ,parent.Name AS parentName
    FROM relationship
    LEFT JOIN people AS child ON child.ID = peopleID
    LEFT JOIN people AS parent ON parent.ID = relatedID
)
SELECT
     t.childID
    ,t.childName
    ,STUFF(ISNULL((
        SELECT ', ' + CAST(x.parentID AS NVARCHAR(10)) + ', ' + x.parentName
        FROM familly x
        WHERE x.childID = t.childID
        GROUP BY x.parentID, x.parentName
        FOR XML PATH (''), TYPE
    ).value('.','VARCHAR(max)'), ''), 1, 2, '') [Parents]
FROM familly t
WHERE t.childID IN(3,6,8,11) 
GROUP BY t.childID, t.childName

There're less LEFT JOIN and more readable. You should start the jointure by using the table relationship, so:

on one side, you can join people as children
on the other side, you can joint people as parents.

Then, I've used the statement WITH to provide better readability. At the end, the operation STUFF (Transact-SQL) concatenates multiple strings (parents) into one row.

References :

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

3 Answers3