Mr_Ezi
asked on
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
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
ASKER
it only give me back the only id 1
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.
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.
ASKER
sorry you right i dint copy now go it over it didn't give me back any think just a empty table
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.
The only text that should be changed in the query is where it says "tbl", to your real table name.
;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
;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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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