CODEWITHSUNDEEP
mysqlsqljoincount
Cozel
Cozel

Reputation: 27

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        |

Upvotes: 2

Views: 575

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 0

Livius
Livius

Reputation: 956

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

your join to employee is not needed at all

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

Upvotes: 1

Shivrudra
Shivrudra

Reputation: 684

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

Upvotes: 2

p.ganesh
p.ganesh

Reputation: 120

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

Upvotes: 0

Related Questions