我的查询输出不太正确，可能是对此的子查询

Question

需要一些帮助来构建我的查询。我想我需要一个子查询，但我不太确定如何在我的上下文中使用它们。我有以下表格和数据，

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

我有以下查询

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')
    )

上面的查询输出以下数据

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

我

有点明白发生了什么，因此我认为我可能需要一个子查询或可能是一个联合来首先获得父母，然后基于这些结果。我正在尝试输出以下内容，有人可以帮忙吗？

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 1

抱歉，我现在无法检查查询。这行得通吗？

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)

关键是要摆脱将（其中 A 或 B）与左连接一起使用。它给结果的逻辑带来了太多的不确定性

Answer 2

这是

你想要的吗？

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

选择 cast（p.id 作为 nvarchar（max）） + '，' + p.name + （ 选择 cast（p1.id 作为 varchar（max）） + '，' + p1.name 从关系 r1 内部加入人员

r1.relatedID = p1.ID 其中 r1.peopleID = p.id 对于 xml 路径 （''） 从关系 r 内部加入人员 p 上 p.ID = r.peopleID 分组按 p.ID，p.name

如果您想要更多，请告诉我。

Answer 3

即使你已经接受了答案，但我仍然想提供我的：

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

LEFT JOIN更少，可读性更强。你应该开始关节通过使用表relationship，因此：

• 一方面，您可以像孩子一样加入people
• 另一方面，您可以作为父母共同people。

然后，我使用语句WITH来提供更好的可读性。最后，操作 STUFF（Transact-SQL）将多个字符串（父项）连接成一行。

引用：

• 堆栈溢出：我可以将多行分隔为一列吗？
• MSDN： STUFF （Transact-SQL）