The approach of finding users that satisfy all conditions need to be solved differently: might be too difficult to realize based on experimenting.
The WHERE-clause conditions will check for a single records (defined relationship). So the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time.
You have two possible solutions.
- Take the query with the OR on CL."CourseTitle" and count if you find users with three hits.
- Create 3 relations from the CL table each for one specific "CourseTitle".
The latter might look like:
- Code: Select all Expand viewCollapse view
SELECT OL."Surename"
FROM "Ops List" OL
INNER JOIN "Training Log" TL ON TL."OpID" = OL."OpID"
INNER JOIN "CourseList" CL1 ON CL1."CourseID" = TL."CourseID"
AND CL1."CourseTitle" ='Asbestos Awareness'
INNER JOIN "CourseList" CL2 ON CL1."CourseID" = TL."CourseID"
AND CL2."CourseTitle" ='Facefit SR100'
INNER JOIN "CourseList" CL3 ON CL1."CourseID" = TL."CourseID"
AND CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE
Note: that the conditions are shifted from the WHERE-clause to the specific JOIN condition: this is to express the tables relation