当前位置：文江博客话题详情

父子链的外连接

发布于 2024-08-29 05:02:47 字数 225 浏览 6 评论 0原文

考虑下面的表格和关系：

父级 --1:Many-- 子级 --1:Many-- subchildren

父级可能有也可能没有子级记录。
孩子总是有子孩子记录。

我想编写一个查询来选择任何匹配的父名称父名称、子名称或子名称。

在这里，我知道我必须在父母和孩子之间进行左外连接。但是我应该在孩子和子孩子之间建立什么样的连接呢？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

飘逸的'云 2024-09-05 05:02:47

如果我理解你的问题，这有效：

declare @theName char(25)
select @theName = 'dave'

SELECT p.id, p.name 
FROM parent p
LEFT OUTER JOIN child c   -- OUTER in case there's no children
    ON p.id = c.parentid
INNER JOIN grandchild g   -- INNER because you say there's always children
    ON c.id = g.parentid
WHERE p.name = @theName
    OR c.name = @theName
    OR g.name = @thename
GROUP BY p.id, p.name     -- GROUP BY to combine multiple hits

编辑（被接受后）添加：我实际上也会使用OUTER作为第二个JOIN ，以防万一规则在没有警告的情况下发生变化。如果不需要的话就不会造成伤害。

If I understand your question, this works:

declare @theName char(25)
select @theName = 'dave'

SELECT p.id, p.name 
FROM parent p
LEFT OUTER JOIN child c   -- OUTER in case there's no children
    ON p.id = c.parentid
INNER JOIN grandchild g   -- INNER because you say there's always children
    ON c.id = g.parentid
WHERE p.name = @theName
    OR c.name = @theName
    OR g.name = @thename
GROUP BY p.id, p.name     -- GROUP BY to combine multiple hits

Edited (after being accepted) to add: I would actually use OUTER for the second JOIN as well, just in case the rules change without warning. It won't hurt if it's not needed.

回复收藏 0 原文

一桥轻雨一伞开 2024-09-05 05:02:47

如果孩子总是有子孩子，你应该使用 INNER JOIN

回复收藏 0 原文

谁把谁当真 2024-09-05 05:02:47

此查询将返回所有父级（包含或不包含子级），并在那些没有任何子级的父级的列中返回空值。

SELECT  *
FROM    Parent p LEFT JOIN
        Children c ON p.ID = c.ParentID LEFT JOIN
        SubChildren sc ON c.ID = sc.ChildID

如果您要将 Children 和 SubChildren 之间的 JOIN 更改为 INNER

SELECT  *
FROM    Parent p LEFT JOIN
        Children c ON p.ID = c.ParentID INNER JOIN
        SubChildren sc ON c.ID = sc.ChildID

那么您将不会从没有任何孩子的父母那里获得行。

This query will return all parent (with or withour children), and return nulls in the columns for those parents that do not have any children.

SELECT  *
FROM    Parent p LEFT JOIN
        Children c ON p.ID = c.ParentID LEFT JOIN
        SubChildren sc ON c.ID = sc.ChildID

If you were to change the JOIN between Children and SubChildren to INNER

SELECT  *
FROM    Parent p LEFT JOIN
        Children c ON p.ID = c.ParentID INNER JOIN
        SubChildren sc ON c.ID = sc.ChildID

Then you will not get the rows from parents that do not hav any children.

回复收藏 0 原文

几度春秋 2024-09-05 05:02:47

SELECT  p.*
FROM    (
        SELECT  id
        FROM    parent
        WHERE   name = 'myname'
        UNION
        SELECT  parent_id
        FROM    child
        WHERE   name = 'myname'
        UNION
        SELECT  c.parent_id
        FROM    child c
        JOIN    grandchild gc
        ON      gc.parent_id = c.id
        WHERE   gc.name = 'myname'
        ) q
JOIN    parent p
ON      p.id = q.id

SELECT  p.*
FROM    (
        SELECT  id
        FROM    parent
        WHERE   name = 'myname'
        UNION
        SELECT  parent_id
        FROM    child
        WHERE   name = 'myname'
        UNION
        SELECT  c.parent_id
        FROM    child c
        JOIN    grandchild gc
        ON      gc.parent_id = c.id
        WHERE   gc.name = 'myname'
        ) q
JOIN    parent p
ON      p.id = q.id

回复收藏 0 原文

~没有更多了~