MySQL Subquery to pull results that meet multiple column data-points-CodePudding

I would like to build a query to pull through results that meet multiple column results from another table.

I have a Table titled 'Engagements' and a Table titled 'Entertainers'. Engagement contains CustomerID and EntertainerId. Entertainers contains EntertainerID and EntertainerName.

Question: List the CustomerIDs for the customers who have booked shows with Entertainer X, Entertainer Y, and Entertainer Z.

I have tried the solution below, but 1) I don't believe that joins are appropriate in the subquery? and 2) Having 2 criteria within the same column (i.e. having booked entertainers X, Y, and Z) seems to be causing me issues. Any advice would be appreciated.

SELECT DISTINCT Engagements.CustomerID
FROM Database.Engagements
WHERE Engagements.CustomerID = ALL (SELECT CustomerID
FROM Database.Engagements
JOIN Database.Entertainers USING (EntertainerID)
WHERE EntertainerName = "X"
OR EntertainerName = "Y"
OR EntertainerName = "Z";

CodePudding user response：

Just count the number of distinct entertainers, if it is 3 then the customer booked with all three entertainers

SELECT CustomerID
FROM Engagements
JOIN Entertainers USING (EntertainerID)
WHERE EntertainerName IN ( 'A', 'B', 'C')
GROUP BY CustomerID
HAVING COUNT(DISTINCT Engagements.EntertainerID) = 3