Let's say I want to find the number of projects the 2 employees Jim Sullivan and Anna Schimdt have worked on together. We have 3 tables employees, workson, project.

Employees

Workson

Projects

From the data sample, Jim Sullivan and Anna Schimdt have worked on 3 projects together. Hence expected outcome should be 3. My code does not show any results for some reason. Here is my code:

SELECT COUNT(w.employeeid) 
FROM workson w 
JOIN employees e ON e.employeeid = w.employeeid
JOIN projects p ON p.projectid = w.projectid
WHERE name LIKE 'jim%sullivan%'
AND name LIKE 'anna%schmidt%';

For some reason, the code works if I only filtered out one employee but as soon as I included both of them, it doesn't work. For example, if I only had Anna Schmidt then results will come out but as soon as I add the AND operator, it does not work. It does work for the OR operator though

CodePudding user response：

With this query:

SELECT w.projectid
FROM Workson w INNER JOIN Employees e
ON e.employeeid = w.employeeid
WHERE (e.name LIKE 'jim%sullivan%') OR (e.name LIKE 'anna%schmidt%')
GROUP BY w.projectid
HAVING COUNT(*) = 2;

you get all the projectids where Jim Sullivan and Anna Schimdt (or Schmidt) have worked together.
I don't know why you use the operator LIKE instead of the operator = to compare the column name to the names of the employees, so I left it as it is, but this would cause problems if for example there is another employee named 'Annabelle Schmidt'.

Use the above query as a subquery to count the rows:

SELECT COUNT(*) count
FROM (
  SELECT w.projectid
  FROM Workson w INNER JOIN Employees e
  ON e.employeeid = w.employeeid
  WHERE (e.name LIKE 'jim%sullivan%') OR (e.name LIKE 'anna%schmidt%')
  GROUP BY w.projectid
  HAVING COUNT(*) = 2
) t; 

Or, for MySql 8.0 use COUNT() window function:

SELECT DISTINCT COUNT(*) OVER () count
FROM Workson w INNER JOIN Employees e
ON e.employeeid = w.employeeid
WHERE (e.name LIKE 'jim%sullivan%') OR (e.name LIKE 'anna%schmidt%')
GROUP BY w.projectid
HAVING COUNT(*) = 2;

See the demo.

The table Projects is not needed.

CodePudding user response：

This will show the projects where Jim did to work on:

SELECT 
   p.projectid, 
   p.projectName
FROM Projects p
INNER JOIN Workson w ON w.projectid = p.projectid
INNER JOIN employees e ON e.employeeid = w.employeeid
WHERE e.name LIKE 'jim%sullivan%'

When you need the project that share 2 workes, you can do something like this:

SELECT 
   p.projectid, 
   p.projectName
FROM Projects p
INNER JOIN Workson w1 ON w1.projectid = p.projectid
INNER JOIN Workson w2 ON w2.projectid = p.projectid
INNER JOIN Employees e1 ON e1.employeeid = w1.employeeid
INNER JOIN Employees e2 ON e2.employeeid = w2.employeeid
WHERE e1.name LIKE 'jim%sullivan%'
  AND e2.name LIKE 'anna%schmidt%';

EDIT: I forgot one JOIN, now it works, see: DBFIDDLE