CODEWITHSUNDEEP
sqloracle-databasesubquery
Fangir
Fangir

Reputation: 151

Oracle correlated subquery

I have table that contains fields:

USER_ID
MANAGER_USER_ID
COST_CENTER

I need to query all of this data, but COST_CENTER should be from manager, so I have made this:

select USER_ID, COST_CENTER, MANAGER_USER_ID
from EMDB e
where COST_CENTER in (
    select COST_CENTER
    from EMDB e2
    where e2.USER_ID = e.MANAGER_USER_ID
    );

Sample data:

USER_ID    MANAGER_USER_ID    COST_CENTER
user1      user3              employeeCostCenter1
user2      user3              employeeCostCenter1
user3      manager3           employeeCostCenter2  <-- this is manager

Sample output:
USER_ID    MANAGER_USER_ID    COST_CENTER
user1      user3              employeeCostCenter2  
user1      user3              employeeCostCenter2  
user3      manager3           costCenterOfManager3

But this doesn't work (returns nothing). Any suggestions?

Upvotes: 1

Views: 45

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Here is how to put the subquery in the select clause:

select 
  user_id, 
  manager_user_id,
  (select cost_center from emdb m where m.user_id = e.manager_user_id) as cost_center
from emdb e
order by user_id;

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this:

SELECT e.user_id, e.manager_user_id, e2.cost_center
FROM emdb e
INNER JOIN emdb e2
ON e2.user_id = e.manager_user_id;

Upvotes: 1

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

Try this:

select e.USER_ID,
       e.MANAGER_USER_ID
       e2.COST_CENTER             
    from EMDB e
      inner join EMDB e2 on e2.USER_ID = e.MANAGER_USER_ID

Upvotes: 1

Related Questions