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.
| Author |
Topic |
|
digory
Starting Member
13 Posts |
 Posted - 2004-02-16 : 05:57:11
|
| I've inherited a problem that I need to solve and, unfortunately I'm not much of a "set-based" thinker.I have a single table named "Ancestors" and also a separate linking table which contains relationships between each of the Ancestoral members: [Ancestors] ( id int, [Name] varchar ) [Relationships] ( id, fkid )So, given an ancestor, I could locate their immediate children by writing a query like this: SELECT a.* FROM Ancestors a INNER JOIN [Relationships] r ON a.id = p.fkid WHERE a.id = ( SELECT b.id FROM [Ancestors] b WHERE [Name] = 'Foo' )That would return all of the children of 'Foo'.What I need to be able to do is to write a single query which can span "up to 7" lines of descendency, that is: Great-Great-Great-Grandparent Great-Great-Grandparent Great-Great-Grandparent Great-Grandparent Grandparent Parent ChildI'm afraid that I have absolutely no idea how to write a single sql query which could filter at any level while still ensuring referential integrity down the line. In other words, I can specify a child which would trace up the tree in a single line, or, specify a Great-Great-Great-Grandparent which would span out from a single point and would show all the way down to leaf nodes regardless of which level they finished at.That last point is important... records which are higher up in the hierarchy should be shown regardless of whether or not they have children!Can anybody give me any pointer to articles or such? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-16 : 10:25:08
|
| Search here for "trees and heirarchies"....you may need to combine the info/structures there with some form of a "coalesce" statement ...search here for samples of that as well! |
 |
|
|
|
|
|
It's valentines day and my relationships suck!