Return all rows where all rows of a child table match all rows of lookup-CodePudding

I need to compare trainers, who have a number of qualifications, against courses, which have a number of This feels like it should be easy but I just can't get my head round it. Simplified example:

[TrainerQuals]
TrainerID    QID
----------------
1            1
2            2
3            1
3            2

[CourseRequiredQuals]
CourseID     QID
----------------
1            1
2            2
3            1
3            2

For a given course ID, list trainers allowed to teach that course, by making sure the trainer has every QID for the course

So, expected output for @courseid = 1

TrainerID
---------
1
3

Expected output for @courseid = 2

TrainerID
---------
2
3

Expected output for @courseid = 3

TrainerID
---------
3

I have tried various join types and conditions but they all give me results where any of the IDs match and no further differences to filter on.

EDIT: Added an SQL Fiddle:

http://sqlfiddle.com/#!18/c852e1/2

CodePudding user response：

A join with aggregation can be used here:

SELECT tq.TrainerID
FROM TrainerQuals tq
LEFT JOIN CourseRequiredQuals crq
    ON crq.QID = tq.QID
WHERE crq.CourseID = 1  -- or 2, or 3
GROUP BY tq.TrainerID
HAVING COUNT(*) = COUNT(crq.QID);

The assertion in the HAVING clause ensures that every trainer record has a matching course record.