Re: IN operator

[Phillip Ward]

It's difficult to be precise without knowing the structure of your tables, but here's how I'd approach it:

Let's assume that your tables looks something like this:

employees 
+-------------+------------+---------------+
| employee_id | manager_id | department_id | 
+-------------+------------+---------------+
|           1 |       NULL |            33 |
|           2 |          1 |            33 |
+-------------+------------+---------------+

departments 
+---------------+-------------+
| department_id | location_id | 
+---------------+-------------+
|            33 |         444 | 
+---------------+-------------+

locations 
+-------------+------------+
| location_id | country_id | 
+-------------+------------+
|         444 | US         |
+-------------+------------+

Then you query ought to look something like this:

select 
  e.first_name
, e.last_name 
from employees e 
inner join employees m 
   on e.manager_id = m.employee_id 
inner join departments d 
   on m.department_id = d.department_id 
inner join locations l 
   on d.location_id = l.location_id 
where l.country_id = 'US' 
order by 1, 2 ;

Regards, Phill W.