Create a self referencing table from join of 2 tables

Question

Post reply

Create a self referencing table from join of 2 tables

Renis Cerga.

SSC Enthusiast

Points: 127
More actions
May 15, 2010 at 12:41 pm

#224520

Hi!
I'm trying to create a query which returns a self referencing table as a product of a join from 2 tables.
First table
ParentID ParentName
1 AAAA
2 BBBB
3 CCCC
4 DDDD
5 EEEE
Second table
ChildID ChildName ParentName (Unfortunatley there isn't ParentID)
1 ABCD AAAA
2 QWER AAAA
3 ASDF BBBB
4 TYUI CCCC
5 POIU CCCC
6 LKJH CCCC
What i need is a self referencing table as follows:
ID Name ParentID
1 AAAA NULL
2 ABCD 1
3 QWER 1
4 BBBB NULL
5 ASDF 4
6 CCCC NULL
7 TYUI 6
8 POIU 6
9 LKJH 6
10 DDDD NULL
11 EEEE NULL
I don't if is possible to do this using sets and not cursors...
Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

For the future, please supply create table and data insert scripts together with question. It will help helper 🙂

That is what you need:

DECLARE @FirstTable TABLE

( ParentID int , ParentName char(4) )

insert @FirstTable

select 1, 'AAAA'

union select 2, 'BBBB'

union select 3, 'CCCC'

union select 4, 'DDDD'

union select 5, 'EEEE'

Declare @SecondTable TABLE

( ChildID int, ChildName char(4), ParentName char(4) )

insert @SecondTable

select 1, 'ABCD', 'AAAA'

union select 2, 'QWER', 'AAAA'

union select 3, 'ASDF', 'BBBB'

union select 4, 'TYUI', 'CCCC'

union select 5, 'POIU', 'CCCC'

union select 6, 'LKJH', 'CCCC'

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

-- QUERY:

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

;with mquery

as

(

select ParentName, cast(NULL as int) as ParentID --, ParentID as OC

from @FirstTable

union

select st.ChildName, ft.ParentID --, ft.ParentID as OC

from @SecondTable as st

join @FirstTable as ft

on ft.ParentName = st.ParentName

)

select ROW_NUMBER() OVER (ORDER BY ParentID) AS ID

,ParentName AS Name

,ParentID AS ParentID

from mquery

I you want your parent nodes to appear first and then its childd, uncomment the commented out code and change the ' OVER (ORDER BY ParentID) ' to ' OVER (ORDER BY OC, ParentID) '

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Renis Cerga. SSC Enthusiast Points: 127 More actions · Answer 2

Perfect!!!!

Thank you so much!

P.S.

I'm sorry, you are absolutely right about example data. Next time i'll use create table and insert!

Renis Cerga. SSC Enthusiast Points: 127 More actions · Answer 3

There is a problem... I modified data as follows:

DECLARE @FirstTable TABLE

( ParentID int , ParentName char(4) )

insert @FirstTable

select 1, 'GGGG'

union select 2, 'BBBB'

union select 3, 'CCCC'

union select 4, 'DDDD'

union select 5, 'EEEE'

Declare @SecondTable TABLE

( ChildID int, ChildName char(4), ParentName char(4) )

insert @SecondTable

select 1, 'ABCD', 'GGGG'

union select 2, 'QWER', 'GGGG'

union select 3, 'ASDF', 'GGGG'

union select 4, 'TYUI', 'GGGG'

union select 5, 'POIU', 'BBBB'

union select 6, 'LKJH', 'BBBB'

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

-- QUERY:

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

;with mquery

as

(

select ParentName, cast(NULL as int) as ParentID , ParentID as OC

from @FirstTable

union

select st.ChildName, ft.ParentID , ft.ParentID as OC

from @SecondTable as st

join @FirstTable as ft

on ft.ParentName = st.ParentName

)

select ROW_NUMBER() OVER (ORDER BY OC, ParentID) AS ID

,ParentName AS Name

,ParentID AS ParentID

from mquery

It mixes ParentID :crying:

Renis Cerga. SSC Enthusiast Points: 127 More actions · Answer 4

Resolved 🙂

It should be

OVER (ORDER BY ParentID, OC)

not

OVER (ORDER BY OC, ParentID)

Thx again!