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

Issue

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

Solution

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

Answered By – Luuk

Answer Checked By – Marilyn (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.