Rabia Mansour (3/18/2007)
Thanks for the article.
My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.
I've added :
It is not reasnable in this example but it is reasnable in other.
How it should be done to accomplish this need.
It's just a question of building the join in the part of the CTE after the Union All.
For example:
;with FTreeCTE (Generation, ID, Parent1ID, Parent2ID, Name) as
(select 1, ID, Parent1ID, Parent2ID, Name
from dbo.FamilyTree
union all
select Generation + 1, ft2.ID, ft2.Parent1ID, ft2.Parent2ID, ft2.Name
from dbo.FamilyTree ft2
inner join FTreeCTE
on ft2.Parent1ID = FTreeCTE.ID
or ft2.Parent2ID = FTreeCTE.ID)
select Generation, ID, Parent1ID, Parent2ID, Name
from FTreeCTE
order by Generation
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon