Join 3 tables and Group

Question

I am stuck here working on a homework and this is what I have got.

Instruction is: Display department name, city and number of different jobs in each department. tables employees (has job_id and department_id), deptos (has location_id, department_id but no job ids), locations (has location_id and city) I need to include all cities even the ones without employees

What I was trying to do...

select d.department_name, l.city, count (distinct e.job_id)
from employees e
join deptos d on (e.department_id=d.department_id)
join locations l on (d.location_id=l.location_id)
group by d.department_name

score 2 · Accepted Answer · answered Oct 09 '13 at 16:24

locations can have data missing in other tables, so right join or you start from it and use left joins. You also need to group by city. Tried to do minimum changes to OP query.

select d.department_name, l.city, count(distinct e.job_id)
from employees e
join deptos d on (e.department_id=d.department_id)
right join locations l on (d.location_id=l.location_id)
group by d.department_name, l.city

SQL Fiddle to test with

score 1 · Answer 2 · edited May 23 '17 at 11:50

1

You need to use a OUTER JOIN to accomplish this... like the below. You do not necessarily have to use the keyword outer as it is implied, just remember the the difference between using LEFT and RIGHT joins there is a post on that here. Example below

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

select d.department_name, l.city, count (distinct e.job_id)
from locations l
left outer join deptos d on (e.department_id=d.department_id)
left outer join employees e on (d.location_id=l.location_id)
group by d.department_name

edited May 23 '17 at 11:50

Community

1
1

answered Oct 09 '13 at 15:50

Jwit

156
6

join conditions need to be swapped and group by missing l.city – Elhana Oct 11 '13 at 14:41

Linger · Answer 3 · 2013-10-11T14:53:20.013

0

SELECT locations.city, deptos.department_name, 
       Count(DISTINCT employees.job_id) AS DiffJobsDeptCount
FROM employees 
     RIGHT JOIN (deptos 
     RIGHT JOIN locations 
     ON deptos.location_id = locations.location_id) 
     ON employees.department_id  = deptos.department_id
GROUP BY locations.city, deptos.department_name

edited Oct 11 '13 at 14:53

answered Oct 09 '13 at 15:43

Linger

14,942
23
52
79

In the OP question, there is `count (distinct e.job_id)` in the query :) – Fabien TheSolution Oct 09 '13 at 15:55
**@Fabien TheSolution**, hey that is where the employees table comes into play. Missed that one. I updated my answer. – Linger Oct 09 '13 at 16:01
Does not display city without departments as OP wanted. Either needs to be RIGHT JOIN twice or without brackets. – Elhana Oct 11 '13 at 14:49

Join 3 tables and Group

3 Answers3