Join 3 tables with Count

I am trying to write a simple query that will display all projects and their total number of team members, sorted alphabetically by project. If a project does not have assigned team members, that project should still be included in the output.

CREATE TABLE Project ( ID INT IDENTITY(1,1), ProjectName VARCHAR(50), DueDate
DATE)
CREATE TABLE Employee ( ID INT IDENTITY(1,1), EmployeeName VARCHAR(50) )
CREATE TABLE ProjectAssignment ( ID INT IDENTITY(1,1), ProjectID INT,
EmployeeID INT)

INSERT INTO Project VALUES ('Alpha', '1/1/2040'), ('Bravo', '3/1/2030'),
('Charlie', '2/1/2017'), ('Delta', '4/1/2017')
INSERT INTO Employee VALUES ('John'), ('Beth'), ('Tom'), ('Kim'), ('Jack')
INSERT INTO ProjectAssignment VALUES (1, 1), (1, 2), (2, 2), (2, 3), (3,
3), (3, 4), (1, 3)

--TABLE Project:
ID ProjectName DueDate
1 Alpha 2040-01-01
2 Bravo 2030-03-01
3 Charlie 2017-02-01
4 Delta 2017-04-01

--TABLE Employee:
ID EmployeeName
1 John
2 Beth
3 Tom
4 Kim
5 Jack

--TABLE ProjectAssignment:
ID ProjectID EmployeeID
1 1 1
2 1 2
3 2 2
4 2 3
5 3 3
6 3 4
7 1 3


Here is my wrong query:

SELECT n.ProjectName, Count(t.ProjectID) as NumMembers
FROM Project p
LEFT JOIN ProjectAssignment t ON p.EmployeeID = t.EmployeeID
LEFT JOIN employee e ON e.ProjectID = t.ProjectID
GROUP BY n.Project
ORDER BY n.Project


Desired Result:

| ProjectName | NumMembers |
+-------------+-------------+
| Alpha | 3 |
| Bravo | 2 |
| Charlie | 2 |
| Delta | null |


4 Answers
4

Please try this Mysql query. This will resolve your issue. We dont' require employee table join. If you are not taking any data from employee table then don't add employee table in join.

SELECT
p.name AS ProjectName,
Count( t.employeeID ) AS NumMembers
FROM
Project p
LEFT JOIN ProjectAssignment t ON p.id = t.projectID
GROUP BY
p.name


Output:

Project name NumMembers
Alpha 3
Bravo 2
Charlie 2
Delta 0


Few things

LEFT JOIN ProjectAssignment t ON p.EmployeeID = t.EmployeeID
is wrong as you can see p.EmployeeID do not exists on table Project

p.EmployeeID

Project

your join to employee is not needed at all

employee

also finally i do not know how your fields names are. you post Name in sample data and projectName is used in your query and ddl (changed to your DDL instead of your query)

SELECT p.ProjectName, Count(DISTINCT pa.EmployeeID) as NumMembers
FROM Project p
LEFT JOIN ProjectAssignment pa ON p.ID = pa.ProjectID
GROUP BY p.ID, p.ProjectName
ORDER BY p.ProjectName


http://sqlfiddle.com/#!18/36df5/1

SELECT n.ProjectName, Count(t.ProjectID) as NumMembers FROM Project n LEFT JOIN ProjectAssignment t ON n.id = t.ProjectID GROUP BY t.ProjectID ORDER BY n.ProjectName


run this query

Just use this ( it seems join conditions are mixed ):

SELECT p.ProjectName, Count(t.ProjectID) as NumMembers
FROM Project p
LEFT JOIN ProjectAssignment t ON p.ID = t.ProjectID
LEFT JOIN employee e ON t.EmployeeID = e.ID
GROUP BY p.ProjectName
ORDER BY p.ProjectName;

ProjectName NumMembers
Alpha 3
Bravo 2
Charlie 2
Delta 0


SQL Fiddle Demo

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.