outer join for parent child chain

Question

Considering below tables and relationships:

parent --1:Many-- children --1:Many-- subchildren

• Parent may or many not have children records.
• children always have subchildren records.

I want to write a query to select parent names where any if matched parent.name,children.name or subchildren.name.

Here I understand I have to do a left outer join between parent and children. But what kind of join should I put between children and subchildren ?

Answer 1

If children always have subchildren you should use INNER JOIN

Answer 2

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.

Answer 3

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

Answer 4

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.