Is it possible to select all dishes that contain only ingredients A and B in one query?

I know you could achieve this by getting all dishes that contain A and B and then checking every dish in the results with a second query that counts how many ingredients each dish has. Then, if the number is 2, it should be a dish of 2 ingredients that contains A and B, so it ONLY contains A and B. Like this:

Getting all dishes that contain A and B.

SELECT dishes.id, dishes.name 
FROM dishes 
JOIN dishes_ingredients ON dishes.id = dishes_ingredients.dishes_id
JOIN ingredients ON dishes_ingredients.ingredients_id = ingredients.id
WHERE ingredients.name IN ('A', 'B')
GROUP BY dishes.id
HAVING COUNT(DISTINCT ingredients.id) = 2;

Getting the number of ingredients in each dish.

SELECT COUNT(ingredients_id) 
FROM dishes_ingredients 
WHERE dishes_id = (each dish in previous query's results - could be also done in PHP)

The problem I see, is that you cannot use in the same query HAVING COUNT(DISTINCT ingredients.id) 2 times, one at the un-selected level, to get the total number of ingredients of a dish, and another at the selected level, to get the number of selected rows given the WHERE you used. I guess this is because HAVING applies to GROUP BY… But maybe I am wrong? If this were possible, then you could check both numbers and get only the dishes that just contain A and B.

Answer

Remove the WHERE clause and set the conditions only in the HAVING clause:

SELECT d.id, d.name 
FROM dishes d 
JOIN dishes_ingredients di ON d.id = di.dishes_id
JOIN ingredients i ON di.ingredients_id = i.id
GROUP BY d.id, d.name 
HAVING COUNT(DISTINCT CASE WHEN i.name IN ('A', 'B') THEN i.name END) = 2
   AND SUM(i.name NOT IN ('A', 'B')) = 0

If each ingredient appears only once for each dish then the HAVING clause can be simplified to:

HAVING SUM(i.name IN ('A', 'B')) = 2
   AND SUM(i.name NOT IN ('A', 'B')) = 0

Another way to do it with the use of GROUP_CONCAT():

SELECT d.id, d.name 
FROM dishes d 
JOIN dishes_ingredients di ON d.id = di.dishes_id
JOIN ingredients i ON di.ingredients_id = i.id
GROUP BY d.id, d.name 
HAVING GROUP_CONCAT(DISTINCT i.name ORDER BY i.name) = 'A,B'

If each ingredient appears only once for each dish then there is no need for DISTINCT.