Parent Child Relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parent Child Relationship

Author

Topic

manojn1707
Starting Member

4 Posts

Posted - 2012-04-14 : 11:19:34

Hi Experts,

I need your help in extracting parent child relationship from two columns. Below is the table structure.

Table Name : test1
No Name
E1 A1
E2 A2
E3 B1
Table Name: test2
Name Parent
A1 A2
A2 A3
A3 NULL
B1 NULL
Expected Result:
No Name Parent 1 Parent2
E1 A1 A2 A3
E2 A2 A3
E3 B1
Can you try to design query for extracting above result .

Regards,

Manoj N

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-04-14 : 17:30:32

where is that you are storing PARENT1 and PARENT2??

manojn1707
Starting Member

4 Posts

Posted - 2012-04-15 : 00:04:55

Hi Ahmed,
Parent 1 , Parent 2 are the values derived from Table test2.

Table Name: test2
Name Parent
A1 A2
A2 A3
A3 NULL
B1 NULL

In the above table ,
1. A2 is the parent of A1,
2. A3 is the parent of A2.
3. B1 is not having any parent.

So in the final result, the parent of every child should be displayed in new columns.

Regards,
Manoj N.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-15 : 13:15:48

have a look at

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manojn1707
Starting Member

4 Posts

Posted - 2012-04-15 : 14:23:19

Hi visakh16,
Thanks for sharing the link. I tried with the below query, but received "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
" error.

; WITH G3
AS
(
select * from test1 join test2 where test1.name = test2.name and
UNION ALL
SELECT * FROM test2 g4 , test2 G2 where G2.NAME = g4.PARENT
) SELECT * FROM G3

Regards,
Manoj N.

manojn1707
Starting Member

4 Posts

Posted - 2012-04-15 : 15:32:04

Hi Visakh,
Thanks for the link. I m able to analyse and design the for table test2 ...

With the below query i m able to retrieve parent child relationship..

; WITH G3
AS
( SELECT G2.NAME, G2.PARENT
FROM TEST2 G2 WHERE G2.NAME = 'A1'
UNION ALL
SELECT G2.NAME, G2.PARENT FROM TEST2 G2 INNER JOIN G3.PARENT = G2.NAME
) SELECT NAME FROM G3

Now i m getting result as ....

NAME
A1
A2
A3

Next jobs is to convert tranpose the values into multiple columns as below...

NAME PARENT1 PARENT2
A1 A2 A3

Regards,
Manoj. N.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-15 : 16:52:37

have a look at pivot operator

http://msdn.microsoft.com/en-us/library/ms177410.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources