jaxicab jaxicab - 7 months ago 21
SQL Question

SQL Multiple inner joins

I am working on a sql query to do the following:

For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project.

So far my query looks like this:

SELECT p.PNO
, p.PNAME
, COUNT( DISTINCT w.ESSN) '# employees from Dept. 5'
FROM project p
JOIN department d
ON d.DNO = p.DNO
JOIN employee e
ON e.DNO = d.DNO
JOIN works_on w
ON w.ESSN = e.SSN
WHERE e.DNO LIKE '5'
AND p.PNO LIKE 10


Where I am testing it for project number 10 which should return the number of employees from Dept. 5 as 1, however it returns NULL. I think that I need to somehow join the project and employee tables but I am unsure

Attached is my schema

ER Diagram

Answer

Your joins are wrong.

You should be joining only projects, works_on and employee tables.

SELECT p.PNO, p.PNAME, COUNT( DISTINCT w.ESSN) '# employees from Dept. 5'
FROM project p
INNER JOIN works_on w 
ON p.pno = w.pno
INNER JOIN employee e 
ON w.essn = e.essn
WHERE e.DNO LIKE '5' AND p.PNO LIKE 10

And you are missing the group by at the end:

GROUP BY p.PNO, p.PNAME