I have two tables and records in it as below:
User
---------------------
ID UserId
---------------------
1 User1
2 User2
Department_User
------------------------------
ID DEPT_ID USER_ID
------------------------------
1 1 1
2 2 1
3 1 2
Now I want a oracle query which will return only those users who are serving in both the departments(1 & 2), in this example it will be 1
This is an example of a set-within-sets query. I like to solve these using group by
and having
. Here is one method:
select user_id
from department_user
where dept_id in (1, 2)
group by user_id
having count(distinct dept_id) = 2;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments