get all child from an parent id

Question

hi i need a query to do this my table data

ID        ParentID         DATA
--------------------------------
1             -1             a
2              1             b
3              2             c
4              3             d
5              3             f

and what ineed a query that take a ID as parameter and return all recursively childs and Itself

parameter : (ID=2)

return must be :

ID        ParentID         DATA
--------------------------------
2              1             b
3              2             c
4              3             d
5              3             f

I'm guessing that you forgot to mention that it has to *recursively* return children, not just the children of the parent, and the parent itself. — Lynn Crumbling, Apr 11 '12 at 16:13
Possible dupe of this (which talks about linq, but the solution is the same; use a cte in sql server): http://stackoverflow.com/questions/3299496/retreive-all-children-and-their-children-recursive-sql — Lynn Crumbling, Apr 11 '12 at 16:18

score 3 · Answer 1 · answered Apr 11 '12 at 16:31

3

Try this:

;with temp as (
  select id, parentId, data from t
  where id = 2
  union all
  select t.id, t.parentId, t.data from t
  join temp on temp.id = t.parentId
)
select * from temp

Fiddle here.

answered Apr 11 '12 at 16:31

Mosty Mostacho

42,742
16
96
123

Taryn · Accepted Answer · 2012-04-11T16:53:04.817

This should do it for you:

create table #temp 
(
    id int, 
    parentid int,
    data varchar(1)
)
insert #temp (id, parentid, data) values (1, -1, 'a')
insert #temp (id, parentid, data) values (2,1, 'b')
insert #temp (id, parentid, data) values  (3,2, 'c')
insert #temp (id, parentid, data) values  (4,3, 'd')
insert #temp (id, parentid, data) values  (5,3, 'f')

; with cte as (
    select  id, parentid, data, id as topparent
    from    #temp
    union all
    select  child.id, child.parentid, child.data, parent.topparent
    from    #temp child
    join    cte parent
    on      parent.id = child.parentid

)
select  id, parentid, data
from    cte
where topparent = 2

drop table #temp

EDIT or you can put the WHERE clause inside the first select

create table #temp 
(
    id int, 
    parentid int,
    data varchar(1)
)
insert #temp (id, parentid, data) values (1, -1, 'a')
insert #temp (id, parentid, data) values (2,1, 'b')
insert #temp (id, parentid, data) values  (3,2, 'c')
insert #temp (id, parentid, data) values  (4,3, 'd')
insert #temp (id, parentid, data) values  (5,3, 'f')

; with cte as (
    select  id, parentid, data, id as topparent
    from    #temp
    WHERE id = 2
    union all
    select  child.id, child.parentid, child.data, parent.topparent
    from    #temp child
    join    cte parent
    on      parent.id = child.parentid

)
select  id, parentid, data
from    cte

drop table #temp

Results:

id  parentid      data
2   1              b
3   2              c
4   3              d
5   3              f

@mX64 I did some testing and it looks like it has better performance if you filter on `ID` in the anchor query. I tested with `ID` as a clustered primary key and a non clustered index on `ParentID`. — Mikael Eriksson, Apr 11 '12 at 16:51
@MikaelEriksson thanks for pointing that out, I updated my answer with both. I agree it is probably better to filter there unless you want all of the parent/child records then don't use the WHERE clause until you are done. — Taryn, Apr 11 '12 at 16:53

score 1 · Answer 3 · edited Jan 18 '21 at 12:30

1

declare @ID int = 2;

with C as
(
  select ID, ParentID, DATA
  from YourTable
  where ID = @ID
  union all
  select T.ID, T.ParentID, T.DATA
  from YourTable as T
    inner join C 
      on T.ParentID = C.ID
)
select ID, ParentID, DATA
from C

Try on SE-Data

edited Jan 18 '21 at 12:30

Community

1
1

answered Apr 11 '12 at 16:30

Mikael Eriksson

136,425
22
210
281

score 0 · Answer 4 · answered Apr 11 '12 at 16:13

0

try this.

select * from table where id= 2 or parentid = 2

answered Apr 11 '12 at 16:13

hkutluay

6,794
2
33
53

1

that won't work because they want the children of children as well. So they want the children of 3 because it is a child of 2. – Taryn Apr 11 '12 at 16:15
oh i see.. http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server may help you. – hkutluay Apr 11 '12 at 16:23

get all child from an parent id

4 Answers4