Parent child relationship in separate tables

Question

Post reply

Parent child relationship in separate tables

kunal.desai 7690

SSC Veteran

Points: 200
More actions
May 17, 2013 at 5:59 am

#293980

Hello everyone,
We are having data with parent child relationship. But they are spread across two tables due to our Business Logic. So overall the data is of following type
ParentTable
Id | Name
-----------
1 | Parent1
2 | Parent2
3 | Parent3
ChildTable
Id | ParentId | Name
--------------------
1 | 1 | Child1of1
2 | 1 | Child2of1
3 | 2 | Child1of2
4 | 3 | Child1of3
Now, I want to create an SQL Query that returns me data in following type.
ID | ParentId | Name
--------------------
1 | NULL | Parent1
2 | 1 | Child1of1
3 | 1 | Child2of1
4 | NULL | Parent2
5 | 4 | Child1of2
6 | NULL | Parent3
7 | 6 | Child1of3
It would be very helpful for me if someone can help please.
Thanks,
K

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Your results are incorrect - they show a parentid which doesn't exist in the table.

WITH ParentTable AS (

SELECT * FROM (VALUES

(1,'Parent1'),

(2,'Parent2'),

(3,'Parent3')) d (Id, Name)

), ChildTable AS (

SELECT * FROM (VALUES

(1,1,'Child1of1'),

(2,1,'Child2of1'),

(3,2,'Child1of2'),

(4,3,'Child1of3')

) d (Id, ParentId, Name)

)

SELECT

ID = ROW_NUMBER() OVER(ORDER BY ID, ParentId),

ParentId,

Name

FROM (

SELECT p.ID, ParentId = NULL, p.Name

FROM ParentTable p

UNION ALL

SELECT p.ID, c.ParentId, c.Name

FROM ParentTable p

INNER JOIN ChildTable c ON c.ParentId = p.Id

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kunal.desai 7690 SSC Veteran Points: 200 More actions · Answer 2

Agreed that ParentId in my results are not in the table, but that is what I want to generate!!

Your query came up with the following result

ID | ParentID | Name

----------------------

1NULLParent1

21Child1of1

31Child2of1

4NULLParent2

52Child1of2

6NULLParent3

73Child1of3

If you see, over here Child1of2 is shown as child of Child1of1. But that's not true. Child1of2 is actually a child of Parent2. Hence the output should be

ID | ParentId | Name

--------------------

1 | NULL | Parent1

2 | 1 | Child1of1

3 | 1 | Child2of1

4 | NULL | Parent2

5 | 4 | Child1of2

6 | NULL | Parent3

7 | 6 | Child1of3

Regards,

K

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

kunal.desai 7690 (5/17/2013)
Agreed that ParentId in my results are not in the table, but that is what I want to generate!!

then you must explain the rules for generating ParentID.

Your query came up with the following result
ID | ParentID | Name
----------------------
1NULLParent1
21Child1of1
31Child2of1
4NULLParent2
52Child1of2
6NULLParent3
73Child1of3
If you see, over here Child1of2 is shown as child of Child1of1. But that's not true. Child1of2 is actually a child of Parent2. Hence the output should be
ID | ParentId | Name
--------------------
1 | NULL | Parent1
2 | 1 | Child1of1
3 | 1 | Child2of1
4 | NULL | Parent2
5 | 4 | Child1of2
6 | NULL | Parent3
7 | 6 | Child1of3
Regards,
K

Apart from the difference in ParentID for rows 5 and 7, my results exactly match yours.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

We may need non set-based methods to achieve the result you need

I have a set-based solution which gives you different results in terms of numbers, but the objective is the same

Does this help?

DECLARE @ParentTable TABLE

(

IdINT,

NameVARCHAR(10)

)

DECLARE @ChildTable TABLE

(

IdINT,

ParentIdINT,

NameVARCHAR(10)

)

DECLARE@maxParent INT

INSERT@ParentTable

SELECT 1, 'Parent1' UNION ALL

SELECT 2, 'Parent2' UNION ALL

SELECT 3, 'Parent3'

INSERT @ChildTable

SELECT 1, 1, 'Child1of1' UNION ALL

SELECT 2, 1, 'Child2of1' UNION ALL

SELECT 3, 2, 'Child1of2' UNION ALL

SELECT 4, 3, 'Child1of3'

SELECT@maxParent = MAX(Id)

FROM@ParentTable

SELECTId AS ID, NULL AS ParentId, Name

FROM@ParentTable

UNION ALL

SELECT@maxParent + Id, ParentId, Name

FROM@ChildTable

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kunal.desai 7690 SSC Veteran Points: 200 More actions · Answer 5

Hey,

Many many thanks,

This worked like a gem. I met my objective with this solution.

Regards,

K

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

kunal.desai 7690 (5/19/2013)
Hey,
Many many thanks,
This worked like a gem. I met my objective with this solution.
Regards,
K

Glad I could help you out 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/