How do I select one parent row and additional rows for its children without a UNION?

Question

I have a parent and child table and want to create a select statement that, given a parent id, returns a row for that parent and additional rows for every child. Doing a left join is not giving me a row for the parent by itself when one or more children exist. I know this can be done with a UNION but I'm looking for a solution that does not use a union statement. Is this possible?

[Parent Table]

ID     Name
-------------
1    | Bob

[Child Table]

ID     ParentId   Name
-----------------------
1    | 1        | Jim    
2    | 1        | Ned

Query result I'm looking for:

Parent_Name   Child_Name
---------------------------
Bob         | NULL <- I need this null here
Bob         | Jim
Bob         | Ned

Answer 1

You need to do this one time table setup (if you don't want to use a Numbers Table, see alternative solution below):

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.columns s1
    CROSS JOIN sys.columns s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Once the Numbers table is set up, use this query:

DECLARE @ParentTable table (ID int,Name varchar(20))
DECLARE @ChildTable table (ID int,ParentID int,Name varchar(20))
INSERT INTO @ParentTable VALUES (1,'Bob')
INSERT INTO @ChildTable VALUES (1,1,'Jim')
INSERT INTO @ChildTable VALUES (2,1,'Ned')

SELECT DISTINCT
    dt.Name,c.Name
    FROM (SELECT
                 CASE WHEN n.Number=1 THEN NULL ELSE p.ID END AS ID,p.Name
              FROM @ParentTable           p
                  INNER JOIN Numbers      n ON 1=1
              WHERE p.ID=1 AND n.Number<=2
         ) dt
        LEFT OUTER JOIN @ChildTable c ON dt.ID=c.ParentID
    ORDER BY 1,2

OUTPUT:

Name                 Name
-------------------- --------------------
Bob                  NULL
Bob                  Jim
Bob                  Ned

(3 row(s) affected)

alternative solution if you don't want to create a Numbers table you can use this method, returns the same output as above:

SELECT DISTINCT
    dt.Name,c.Name
    FROM (SELECT
                 CASE WHEN n.Number=1 THEN NULL ELSE p.ID END AS ID,p.Name
              FROM @ParentTable           p
                  INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS Number FROM sys.columns) n ON n.Number<=2
              WHERE p.ID=1 AND n.Number<=2
         ) dt
        LEFT OUTER JOIN @ChildTable c ON dt.ID=c.ParentID
        ORDER BY 1,2

I was trying to use A CTE to expand the parent row, but it is hard without using a UNION!

Answer 2

Have a dirty DIRTY hack:

SELECT
    P2.Name Parent_Name,
    C.Name Child_Name
FROM [Parent Table] P1
    FULL OUTER JOIN [Child Table] C
     ON 1=0
    INNER JOIN [Parent Table] P2
     ON IsNull(P1.ID,C.ParentId) = P2.ID
WHERE P2.ID = *ID here*

This should give you the results you want... hopefully.

Answer 3

foriamstu answer inspired me to look for an even simpler answer:

DECLARE @ParentTable table (ID int, Name varchar(20))
DECLARE @ChildTable table (ID int,ParentID int, Name varchar(20))
INSERT INTO @ParentTable VALUES (1,'Bob')
INSERT INTO @ChildTable VALUES (1,1,'Jim')
INSERT INTO @ChildTable VALUES (2,1,'Ned')

SELECT DISTINCT
    P1.Name     Parent_Name,
    C.Name      Child_Name
FROM @ParentTable P1
LEFT JOIN @ChildTable C2 ON C2.ParentID = P1.ID
LEFT JOIN @ChildTable C ON C.ParentID = P1.ID AND C.ParentID = C2.ID
WHERE P1.ID = 1