Parent - child sql query
id parent_id
1 0
2 0
3 2
4 0
5 1
6 0
I need a query that will return the parent rows (parent_id = 0) and then its child rows:
- first parent
- all children of the first parent
- second parent
- all children of the second parent
- third parent
- fourth parent
Expected Result: ordered by id
id parent_id
-------------------------------------------
1 0 (first parent)
5 1 (all children of first parent)
2 0 second parent
3 2 (all children of second parent)
4 0 third parent
6 0 fourth parent
I can use the union of parents, followed by all children. But that gives me parents first children. I need a parent and immediately his children.
Anyone can help?
source to share
This can be accomplished using two temporary tables and three variables.
CREATE TABLE #Parents
(
RowId bigint identity (1,1),
Id bigint
)
CREATE TABLE #Results
(
RowId bigint identity (1,1),
Id bigint,
ParentId bigint
)
DECLARE @Count1 bigint
DECLARE @Count2 bigint
DECLARE @ParentId bigint
INSERT INTO #Parents
SELECT
FROM MyTable
WHERE ParentId = 0
ORDER BY Id
SET @Count1 = 0
SELECT @Count2 = MAX (RowId) FROM #Parents
WHILE @Count1 < @count2
SET @Count1 = @Count1 +1 SELECT @ParentId = Id FROM #Parents WHERE RowId = @Count1 INSERT INTO #Results (Id, ParentId) (@Count1, 0) INSERT INTO #Results (Id, ParentId) SELECT ID, ParentId FROM MyTable ID = @Count1 ORDER BY Id
END
SELECT
,
ParentId
FROM #
ORDER BY RowId
DROP #
DROP #Parents
source to share
If you are on SQL Server 2005+, you can use a recursive CTE, making sure you support the field you can order at the end.
Try the following:
declare @t table (id int, parent_id int)
insert @t
select 1,0
union all select 2,0
union all select 3,2
union all select 4,0
union all select 5,1
union all select 6,0
;
with tree as (
select t.*, convert(varbinary(max),t.id) as ordered
from @t t
where parent_id = 0
union all
select t.*, ordered + convert(varbinary(max),t.id)
from tree base
join
@t t
on t.parent_id = base.id
)
select *
from tree
order by ordered
;
source to share