CODEWITHSUNDEEP
sqloracle-databaseselect
Mark
Mark

Reputation: 273

ORACLE conditionally select multiple rows

Having such table:

CREATE TABLE Emp
    (manager varchar(14), sub varchar(14))
;

INSERT ALL 
    INTO Emp (manager, sub)
         VALUES ('boss', 'john')
    INTO Emp (manager, sub)
         VALUES ('boss', 'eric')
    INTO Emp (manager, sub)
         VALUES ('boss', 'anna')
    INTO Emp (manager, sub)
         VALUES ('boss1', 'boss')
    INTO Emp (manager, sub)
         VALUES ('boss1', 'kris')
    INTO Emp (manager, sub)
         VALUES ('boss1', 'dave')
SELECT * FROM dual
;

and given a name of an employee (sub) I want to have returned names as follows:

This one works only if the emplyee is a manager:

SELECT sub FROM Emp
WHERE manager = 'boss' OR sub ='boss'
;

So basically if the given employee is 'john' or 'boss' the data should be like this:

john
eric
anna
boss

Upvotes: 3

Views: 867

Answers (2)

YLG
YLG

Reputation: 885

  select manager from emp where sub in (:name) and manager in (select sub from emp) 
 union
select sub from emp where manager in (select manager from emp where sub=:name and sub=manager) 
 union 
select sub from emp where manager in (select :name from emp )
 union
select sub from emp where manager in (select manager from emp where sub=:name and manager in (select sub from emp))
 union
select :name from dual;

Here name is bind variable. I tried with both John and Boss. It worked for me. It gave me data as you expected.

Upvotes: 1

Ori Marko
Ori Marko

Reputation: 58872

Get all manager team and add manager to them:

with man as (
 select manager as sub from emp where sub = 'john'
 )
select Emp.sub from Emp,man where manager = man.sub    
union all
select sub from man

If you identify boss as manager value not 'boss', you can get team for boss/sub

with man as (
 select manager as sub from emp where sub = 'john'
 )
select Emp.sub from Emp,man where manager = decode(Emp.manager,'boss',man.sub, Emp.sub)    
union all
select sub from man

EDIT SQL Without alias

with man as (
 select manager from emp where sub = 'john'
 )
select Emp.sub from Emp,man where manager = decode(Emp.manager,'boss',man.manager, Emp.sub)    
union all
select manager from man

EDIT remove superior's team:

with man as (
 select manager,sub from emp where sub = 'john'
 )
select Emp.sub from Emp,man where Emp.manager = decode(Emp.manager,'boss',Emp.manager, Emp.sub)    
union all
select manager from man where   sub <>'boss'
union all
select sub from man where   manager <>'boss'

Upvotes: 1

Related Questions