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.
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
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)