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

SQL Server 2008 Forums

Transact-SQL (2008)

parent child relationship

Author

Topic

sura
Starting Member

10 Posts

Posted - 2012-03-22 : 00:46:47

Hello All

I have two tables like below. This is something like parent child relationship. Table 1 have relation details and Table 2 have ids for each row.

Table 1

Col1 Col2 col3
A A1 A1.1
B B1 B1.1
B B1 B1.2
C C1 C1.1
C C2 C2.1
C C3 C3.1

Table 2
Col1 KEY
A 1
A1 2
A1.1 3
B 4
B1 5
B1.1 6
B1.2 7
C 8
C1 9
C1.1 10
C2 11
C2.1 12
C3 13
C3.1 14

Extecpted output

CHILD PARENT

14 13
12 11
10 9
13 8
11 8
9 8
7 5
6 5
5 4
3 2
2 1
NULL 1

I am not good to write this as a query and it would be great if any one can help me in this.

Thanks
SURA

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-03-22 : 14:53:08

[CODE]select distinct c2.Key Child, p2.Key Parent -- Gather Col1, Col2 key combinations
from Table1 t1
inner join Table2 p2
on t1.Col1 = p2.Col1
inner join Table2 c2
on t1.Col2 = c2.Col1

union all

select distinct c2.Key, p2.Key -- Gather Col2, Col3 key combinations
from Table1 t1
inner join Table2 p2
on t1.Col2 = p2.Col1
inner join Table2 c2
on t1.Col3 = c2.Col1

union all

select null, 1 -- For no discernable reason[/CODE]
=================================================
It is not so much our friends' help that helps us as the confident knowledge that they will help us. -Epicurus, philosopher (c. 341-270 BCE)

sura
Starting Member

10 Posts

Posted - 2012-03-22 : 23:19:54

Thanks Bustaz Kool.

Its really cool.

Regards
SURA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources