I have a basic sql table with an ID and an optional parentID from the same table. This has multiple levels. I need to get a list of all IDs with each of its parents all the way to the top.
if object_id('tempdb..#table') is not null drop table #table
create table #table (id int identity(1,1), [Name] varchar(16), ParentID int null)
insert into #table
values
('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)
+----+--------+----------+
| id | Name | ParentID |
+----+--------+----------+
| 1 | John | NULL |
| 2 | Doe | 1 |
| 3 | Mike | 1 |
| 4 | Neil | 3 |
| 5 | Sylvia | 4 |
| 6 | Mary | 2 |
+----+--------+----------+
I need to get a list like this:
ID | Parent | DirectParent
_______________________
2 1 1
3 1 1
4 3 1
4 1 0
5 4 1
5 3 0
5 1 0
- It ignores any record that does not have a parent.
- For each child there is a record matching it to one of its parents
- There is a bit column to know which parent is the one directly above it
How can I query the above table to get this data?
Edit: see what I have been working with
WITH Recursives AS (
SELECT CAST(ID AS VARCHAR(MAX)) + '\' ID,*
FROM table
WHERE id=@ID
UNION ALL
SELECT r.ID + CAST(t.ID AS VARCHAR(MAX)) + '\', t.*
FROM table t
INNER JOIN Recursives r ON t.parentID= r.ID)
SELECT ID,Name,Parent
FROM Recursives r
I use this to get a list for a specific parent all the way down.
- I can't figure out how to do this for the entire table without
getting an error
The statement terminated. The maximum
recursion 100 has been exhausted before statement completion. - How can I get the same child multiple times. Once with each parent?
Best Answer
you just cannot do recursive on millions of rows.you have to put some filter
Try this,