get all children in a parent child table

i have a table with 3 Fields Name ,ID ,ParentID  I'm looking for sql statement which should give me back all the children id which belong the the parent for example
Name      ID      ParentID
A      1      Null
B      2      1
C      3      1
D      4      2
E      5      2
F      6      3
G      7      3
H      8      9
I       9      8
J      10      8
K      11      8
L      12      10
now i need a sql which i will give (1) and the result should be
2
3
4
5
6
7
this all the children and grandchildren from id 1
Mr_EziAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
parthmalhanConnect With a Mentor Commented:
Try the following code:

--This will create a temp table and insert values in it
create table #t(name varchar(max),id int,pid int)
insert into #t
select 'muni',1,null union all
select 'yash',2,1 union all
select 'gob',3,1 union all
select 'gop',4,1 union all
select 'ash',5,2 union all
select 'vim',6,2 union all
select 'man',7,3 union all
select 'bab',8,3 union all
select 'nee',9,4 union all
select 'nin',10,4

select * from #t

--real Query
with tree(Child,Text,Parent) as
(
 select id,name,pid from #t
 where pid=1 --put parent id here
 union all
 select z.id,z.name,z.pid from  #t z
 inner join tree on tree.Child=z.pid
)select * from tree

Open in new window

0
 
cyberkiwiCommented:
;with cte as (
select id from tbl where id=1
union all
select tbl.id from cte, tbl where tbl.parentID=cte.id)
select id from cte
where id != 1
order by id
0
 
Mr_EziAuthor Commented:
it only give me back the only id 1
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
cyberkiwiCommented:
Mr Ezi,
Are you sure you are copy-pasting/modifying correctly?
The 2nd to last line specifically says "where id != 1" so it should never return 1, regardless of what else it returns.
0
 
Mr_EziAuthor Commented:
sorry you right i dint copy now go it over it didn't give me back any think just a empty table
0
 
cyberkiwiCommented:
If you are modifying the query in any significant way, you need to show us the full query you have used. Did you copy and paste verbatim, and are the fields exactly as shown in the question?

The only text that should be changed in the query is where it says "tbl", to your real table name.
0
 
Ephraim WangoyaCommented:

;WITH CTE(Name, ID, ParentID)
AS
(
      SELECT Name, ID, ParentID
      FROM TABLE1
      WHERE ParentID = 1
      UNION ALL
      SELECT A.Name, A.ID, A.ParentID
      FROM TABLE1 A
      INNER JOIN CTE B ON B.ID = A.ParentID
)
SELECT Name, ID, ParentID
FROM CTE
0
 
_DJCommented:

;WITH CTE(ID)
AS
(
      SELECT ID FROM YourTableName WHERE ParentID = 1
      UNION ALL
      SELECT YT.ID FROM YourTableName YT INNER JOIN CTE TC
		 ON TC.ID = YT.ParentID
)

SELECT ID FROM CTE ORDER BY ID

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.