April 28, 2010 at 2:35 am
Hi Friends,
I need a small help. I have the below table with sample data:
create table test
(
Child varchar(30),
Parent varchar(30)
)
go
insert into test
select 'A','W'
union all
select 'B','W'
union all
select 'W','X'
union all
select 'C','Y'
union all
select 'Y','Z'
--SELECT * FROM TEST
The result I want is, all the child (By child I mean it is one which is there only in Child column but not in Parent column) and their corresponding Final Parent. For example here if we take A or B their final parent is X (through W) . As W is there in Parent I cant consider it as a child .
So Child is : SELECT Child FROM TEST where child not in (select distinct Parent from Test)
Result:
Child
A
B
C
The final result want I should get it is
ChildParent
AX
BX
CZ
I tried with Cross Apply, but the number of level of Child- Parent can be any number of level, so I think we have to go with CTE , but Im not able to make it out.
Note: not sure whether you are getting confused, if so I can explain it again.
Thanks & Regards,
MC
April 28, 2010 at 3:40 am
Hey there, the following code will get you what you want
NOTE : This is not my code, if found it somewhere in this forum; can't remember who coded it. So all credit goes to the original coder.
;With prnt (id, mpid,lvl) AS
(Select d.Child,isnull(d.parent,d.Child),1
From TEST d
UNION ALL
Select d.Child, p.mpid,p.lvl+1
From TEST d
Inner Join prnt p
on d.parent = p.id
)
Select p.id,p.mpid
from prnt p
Inner Join (select id, max(lvl) lvl From prnt group by id) mx
on mx.id = p.id and mx.lvl = p.lvl
order by 1
OPTION (MAXRECURSION 200);
Hope this helps you...
Cheers!!
April 28, 2010 at 9:48 am
Many thanks to you as well as the one who posted it initially and those who tried to help me.
I can work around it and make to get the exact result I need.Thanks once again.
Thanks & Regards,
MC
April 28, 2010 at 8:34 pm
Welcome, Mithun!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply