连接父表和子表的最佳方式 [英] Best way to join parent and child tables

问题描述

我有父表 A 。
A 有几个子表，例如 B ， C ， D ， E ， F code> G
子表不互相链接。它们只链接到 A 。
A 有一个键 Id ，用作所有子表中的外键。

I have Parent table A. A has few child tables such as B,C,D,E,F,G The child tables are not linked to each other. They are only linked to A. A has a key Id which is used as foreign key in all the child tables.

应该是什么是最好的方式来连接这些表，所以我可以创建一个单一的视图呢？

What should be the best way to join these tables so I can create a single view on this?

推荐答案

由于父级在某些表中可能有子行，因此必须使用LEFT OUTER JOIN。

Since a parent may have a child row in some of those tables you must use LEFT OUTER JOIN.

LEFT OUTER JOIN连接两个返回LEFT表的所有行的表，在本例中为A和其他表中的所有匹配项。当没有匹配时，它将在没有匹配的表的相应列中返回NULL。

LEFT OUTER JOIN joins two tables returning all the rows of the LEFT table, in this case A and all the matches from the other tables. When there is no match it will return NULL in the corresponding columns of the tables that there was no match.

SELECT *
FROM A
LEFT OUTER JOIN B
    ON A.Id = B.ParentID
LEFT OUTER JOIN C
    ON A.Id = C.ParentID
    LEFT OUTER JOIN P
        ON C.Id = P.ParentID
    LEFT OUTER JOIN Q
        ON C.Id = Q.ParentID
LEFT OUTER JOIN D
    ON A.Id = D.ParentID
LEFT OUTER JOIN E
    ON A.Id = E.ParentID
LEFT OUTER JOIN F
    ON A.Id = F.ParentID
    LEFT OUTER JOIN X
        ON F.Id = X.ParentID
    LEFT OUTER JOIN Y
        ON F.Id = Y.ParentID
LEFT OUTER JOIN G
    ON A.Id = G.ParentID

EDIT

我添加了一个添加子字符的方法。我想让他们更加只是为了使它们在视觉表示中显而易见。但是要小心...如果这导致子子孙有其他子子孙等也许你的结构不是最佳。

I have added a way to add subchilds. I have intented them more just to make them obvious in a visual representation. But beware...if this lead to subchildren have other subchildren etc maybe your structure is not optimal.

这篇关于连接父表和子表的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！