Recurrsive Relation in a table

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recurrsive Relation in a table

Author

Topic

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2002-02-20 : 10:49:18

I have a table with the following fields:
ID - INT
ParentID - INT
Descript - VARCHAR

The Parent ID is recurssive back to the ID field, here is a sample of data:
ID ParentID Descript
1 0 Something
2 1 Something
3 1 Something
4 2 Something
5 2 Something
6 1 Something
7 3 Something
8 7 Something

Is there a query that I can use to get the following from that table?
(Note: I already created a strored proc to loop through the table and get the data in this format, but was wondering if there was a better way and I could create a view out of a single query.)

ID ParentID
1 3
1 4
1 5
1 6
1 7
1 8
2 4
2 5
3 7
3 8
7 8

I want to see all of the children that each parent has down each level, and flatten the table.

Thanks,
Scooter McFly

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-20 : 11:17:31

You need to perform a SELF JOIN to acomplish your tesk.

SELECT *
FROM myTable t1 INNER JOIN myTable t2
ON t1.id=t2.parentID
ORDER BY t1.id

helena

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-20 : 11:54:22

That would only get you the 1st generation children, not grandchildren or anything further. If you know that you have a maximum depth (say, 3 levels), you could extend Helena's suggestion by self-joining the tables 3 times. I think it would also require a UNION, one for each generation:

SELECT P.ID, C.ID
FROM myTable P INNER JOIN myTable C
ON P.id=C.parentID
UNION
SELECT P.ID, C.ID
FROM myTable P INNER JOIN myTable t1 ON P.ID=t1.parentID
INNER JOIN myTable C ON t1.id=C.parentID
UNION
SELECT P.ID, C.ID
FROM myTable P INNER JOIN myTable t1 ON P.ID=t1.parentID
INNER JOIN mytable t2 ON t1.id=t2.parentID
INNER JOIN myTable C ON t2.id=C.parentID
UNION
SELECT P.ID, C.ID
FROM myTable P INNER JOIN myTable t1 ON P.ID=t1.parentID
INNER JOIN mytable t2 ON t1.id=t2.parentID
INNER JOIN mytable t3 ON t2.id=t3.parentID
INNER JOIN myTable C ON t3.id=C.parentID
ORDER BY P.ID

As you can see this can quickly get unwieldly. I think that the SP that loops is better, especially if the depth exceeds 4 generations.

Can you post the code you're using now?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-21 : 15:04:03

Hey McFly!

You also might want to read these articles on trees [url]http://www.sqlteam.com/SearchResults.asp?SearchTerms=trees[/url]

------------------------
GENERAL-ly speaking...

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2002-02-22 : 08:19:22

Thanks guys, I went with the solution of looping over the table since i can have up to 10 to 20 levels in this table.

Appreciate the articles,
Scooter McFly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources