September 10, 2009 at 9:54 am
Hi,
I'm sure this has been dealt with elsewhere but I can't work out the correct search terms to find an answer!
I have a table with two columns in in: ParentId and ChildId. The table links to itself so any Id may be a child and a parent. A child may have many parents and a parent may have many children. It can be assumed that there will be no loops - i.e. a parent can't be one of it's own decendants...
I need to write something that will return a table of the top parents (i.e. those that are not children in the table) for a given child...
Anyone got any solutions?
Thanks
Gareth
September 10, 2009 at 11:11 am
I believe the standard way for these hierarchies to work is something like:
ID ParentID
1
2 1
3 2
4 1
5 3
2 is a child of 1, 3 is a child of 2, 5 is a child of 3, etc. etc.
If this is the case in your environment, you simply:
SELECT ID FROM table where ParentID IS NULL
Going 1 step farther to take care of orphaned children:
SELECT ID from Table WHERE ParentID IS NULL OR ParentID NOT IN (Select ID FROM Table WHERE ID IS NOT NULL)
If that's not how you have it set up, please see the link in my signature and post some sample data/table structure.
September 10, 2009 at 11:16 am
From the table description, a child can only have one parent, but a parent can have many children. (That's how ParentID, ChildID in the same table works.) From the functional description, you're talking about a many-to-many join (children with multiple parents and parents with multiple children), which would mean you have to have two tables (primary table and join table).
Which one is correct?
- 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
September 11, 2009 at 2:36 am
Garadin - that's fine to get the top parents but I need to get the top parents given a child id.
GSquared - you're right, this is a join table.
Gareth
September 11, 2009 at 2:56 am
Recursive CTE's are the way to go, have a quick google.
Feel free to post back with any further questions
September 11, 2009 at 6:41 am
This article will probably help you wrap your head around the whole thing:
http://www.sqlservercentral.com/articles/T-SQL/65540/
- 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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply