SQL & PL/SQL

Hi All,

I need to write a select query that would accept 'dept' names (one or many) and then return employee names if and only if the employee belongs to all the depts passed.

create table employee (id number, name varchar2(100));

insert into employee (id,name) values (1,'A');
insert into employee (id,name) values (2,'B');
insert into employee (id,name) values (3,'C');


create table emp_dept (emp_id number, dept varchar2(10));

insert into emp_dept (emp_id, dept) values (1,'HR');
insert into emp_dept (emp_id, dept) values (1,'Admin');
insert into emp_dept (emp_id, dept) values (1,'Support');
insert into emp_dept (emp_id, dept) values (2,'HR');
insert into emp_dept (emp_id, dept) values (2,'Support');
insert into emp_dept (emp_id, dept) values (3,'Admin');


e.g.
if dept is 'HR', the query should not return anything
if dept is 'Admin', the query should return only 'C'
if dept is 'Admin' & 'Support', the query should return only 'C'
if dept is 'HR' & 'Support', the query should return only 'B'


I am not sure what conditons I can apply or if query nesting might help. So, any help is appreciated.



Thanks
A