outer join for parent child chain

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 ?

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.

If children always have subchildren you should use INNER JOIN

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.

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