Best Way To Join Parent And Child Tables

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

Solution 1:

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

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
LEFTOUTERJOIN B
    ON A.Id = B.ParentID
LEFTOUTERJOIN C
    ON A.Id = C.ParentID
    LEFTOUTERJOIN P
        ON C.Id = P.ParentID
    LEFTOUTERJOIN Q
        ON C.Id = Q.ParentID
LEFTOUTERJOIN D
    ON A.Id = D.ParentID
LEFTOUTERJOIN E
    ON A.Id = E.ParentID
LEFTOUTERJOIN F
    ON A.Id = F.ParentID
    LEFTOUTERJOIN X
        ON F.Id = X.ParentID
    LEFTOUTERJOIN Y
        ON F.Id = Y.ParentID
LEFTOUTERJOIN 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.

Solution 2:

select <wanted columns>
from a
left join b
on a.id = b.a_id
left join c
on a.id = c.a_id
left join d
on a.id = d.a_id