Query for one-to-many relationship

[lonewatcher]

Hi all...

Let's say that I have a database with two tables: departments (dep_id, dep_name) & employees (emp_id, emp_name, dep_id). I need to select the departments that have the employees with ids 10 AND 20. How do I do that with SQL?

Note: The following is NOT what I want:

select * from departments
left join employees on employees.dep_id = departments.dep_id
where employees.emp_id in (10, 20)

Because this will select the department having employees with ids 10 OR 20.

Thanks in advance.

[lonewatcher]

Can anyone please help me with this?

Thanks.

[dickersonka]

Just to be sure you have a table named employee that have duplicate id's of 10 and 20?

[lonewatcher]

I am not sure exactly what you mean by 'duplicate', but I will try to explain.
I have a table named 'employees', where each entry has a unique ID (emp_id). Also, each entry has a foreign key (dep_id) that refers to an entry in the 'departments' table.
My question is: how can I select an entry from the 'departments' table, given that this 'department' must be referred to by two 'employees', having id's 10 and 20 - for example, not necessarily :-).

[dickersonka]

select dep_id from employees
where emp_id = 10 or emp_id = 20
group by dep_id
having count(dep_id) >= 2

if you need dep_name just do a join on the results to the departments table to get it