1

Please help.

I have one table called Employee.

I want all the record from that table with self join with parentid

record are like

A is parent
B is child of A
C is child of A
D is child of C
F is child of B
E is Parent
G is child of E
H is child of G

if i put self join and put all the record where A is parent and i am only get A,B,C but not D and F

I want all the record where A is parent like A,B,C,D,E .

John Saunders
  • 160,644
  • 26
  • 247
  • 397
satish kumar
  • 443
  • 1
  • 3
  • 10
  • You want to recursively retrieve all child items, or just two levels? – Kirk Broadhurst Apr 18 '13 at 01:08
  • @KenWhite -- not sure that post is the same. SQL Server supports recursive CTEs while MySQL does not. The only true way to do it in MySQL is with dynamic SQL. With that said, I'm sure similar questions have been asked before. – sgeddes Apr 18 '13 at 01:30
  • @Sgeddes: Ok. Use [Hierarchal Queries in SQL Server 2005](http://stackoverflow.com/q/235515/62576) or [hierarchal data from self referencing table in tree form](http://stackoverflow.com/q/2822903/62576) or [How to retrieve hierarchal data from a SQL table](http://stackoverflow.com/q/11230693/62576) instead. :-) It's a duplicate. – Ken White Apr 18 '13 at 01:35

2 Answers2

3

You'll need to use a recursive cte for this if there isn't a set number of parent/child levels. Assuming you're using SQL Server 2005 or greater, this should be what you're looking for:

with cte as (
  select id, 
    id parentid
  from employee
  where parentid is null
  union all
  select e.id, 
    c.parentid
  from employee e
    join cte c on e.parentid = c.id
  )
select id 
from cte
where parentid = 'A'

SQL Fiddle Demo

BTW -- this results in A, B, C, D, and F -- not E. I assume that was a typo in your post.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
0
select distinct(e.ParentName) from Employee e left join Employee e1 
on  e.employeeId=e1.ParentID
Suraj Shrestha
  • 1,790
  • 1
  • 25
  • 51