Geek Answers Handbook

How to choose one parent row and extra rows for my children without UNION?

I have a parent and child table and you want to create a select statement that, given the parent identifier, returns a row for this parent and additional rows for each child. Performing a left join does not give me a row for a parent when one or more children exist. I know that this can be done using UNION, but I am looking for a solution that does not use the union statement. Is it possible?

[Parent table]

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

[Table of children]

 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
+4
source share
3 answers

You 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.

+7
source

You need to make this setting of one temporary table (if you do not want to use a number table, see an 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 configured, 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 do not want to create a Numbers table, you can use this method, it returns the same result 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 tried to use CTE to expand the parent line, but this is difficult without using UNION!

+2
source

the foriamstu answer inspired me to find 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
0
source

More articles:


All Articles