[RESOLVED] Joining tables

[lalloopan]

I have 3 tables:
1. employees (includes all employees in the company) with columns: emp_id, emp_name
2. departments (includes all departments in the company) with columns: dept_id, dept_name
3. mapping (contains employee to department relation) with columns: emp_id, dept_id, emp_title (manager or non-manager)

Table employees
[FONT=Courier New]emp_id emp_name
1 John Doe
2 Kim Hughes
3 Ryan Dunne
4 Bob Taylor
5 Geoff Hunt
6 Meghan Smith
7 Fred Tyson
8 Laura Cheese
9 Rani Hart
10 Ben Holder[/FONT]

Table departments
[FONT=Courier New]dept_id dept_name
1 IT
2 Sales
3 Research[/FONT]

Table mapping
[FONT=Courier New]emp_id dept_id emp_title
1 3 non-manager
2 2 non-manager
2 3 non-manager
3 1 non-manager
4 2 manager
4 3 manager
4 1 non-manager
5 1 non-manager
6 1 non-manager
7 3 non-manager
8 1 non-manager
9 2 non-manager
10 1 manager[/FONT]

I need to create a list of all employees (and their departments) that are managed by any given employee. For example, Bob (emp_id=4) works in three departments (dept_id=1, dept_id=2, dept_id=3) and is manager of two departments (dept_id=2, dept_id=3). How to get the department names, employee names and employee IDs of employees who are managed by Bob?

Here is what I tried but it does not return the correct list:

SELECT mapping.dept_id, departments.dept_name, employees.emp_name, employees.emp_id
FROM mapping, departments, employees
WHERE (
   mapping.emp_id = '4'
   AND departments.dept_id = mapping.dept_id
   AND mapping.emp_title = 'manager'
   AND mapping.emp_id = employees.emp_id
)
GROUP BY departments.dept_name

Thanks

[NogDog]

I think this might work, but have not tested it as I'm too lazy to set up a test DB:

select
	departments.dept_name,
	emp1.emp_id,
	emp1.emp_name
from employees as emp1
inner join mapping as map1 on emp1.emp_id = map1.emp_id
inner join departments on map1.dept_id = departments.dept_id
where departments.dept_id in(
	select dept_id
	from mapping as map2
	inner join employees as emp2 on map2.emp_id = emp2.emp_id
	where emp2.emp_name = 'Bob Taylor'
) and map1.emp_title = 'non-manager'

[lalloopan]

Brilliant. Thanks, NogDog.