How do i find the number of projects that two employees with specific names that have worked together? (using LIKE operator )

Question

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

employeeid	name
110	Jim Sullivan
111	Anna Schimdt
112	James Lee

Workson

projectid	employeeid
6554	110
6554	111
6555	110
6555	111
6556	110
6556	111
6556	112

Projects

projectid	projectName
6556	POPS
6555	BABY
6554	MAMA

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

Answer 1

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.