November 7, 2005 at 6:42 pm
Hi guys,
please use the following script:
create table emp
(
mgr_name varchar(50),
emp_name varchar(50)
)
insert into emp values ('sonia','manmohan')
insert into emp values ('manmohan','arjunsingh')
insert into emp values ('manmohan','natwar')
insert into emp values ('manmohan','chidambaram')
insert into emp values ('chidamabram','palanimanickam')
insert into emp values ('natwar','prabhu')
insert into emp values ('manmohan','laloo')
insert into emp values ('laloo','r.t.velu')
Suppose I need all the employees working under 'manmohan', I should get the following people : arjunsingh,natwar,chidambaram,laloo,prabhu,palanimanickam,r.t.velu
I need a simple SQL solution and a BIG NO to cursors and stored procedures.
Is this possible?
Hari
November 8, 2005 at 4:39 am
Try this initially (quick and dirty solution). It will work for the data you have (noted typo on chidambaram/chidamabram on your INSERTs), however not if you had another level of management (i.e. if prabhu had someone reporting to them, that person would not be returned).
SELECT emp_name
FROM emp
WHERE mgr_name = 'manmohan'
UNION ALL
SELECT emp_name
FROM emp
WHERE mgr_name IN (SELECT emp_name FROM emp WHERE mgr_name = 'manmohan')
November 8, 2005 at 6:13 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply