How to perform Many to Many Relationship Filter Query

I want to perform a query in MySQL based on a filter. First of all, the two tables that will be used in the query are the following:

Equipment:

Field	Type
Id	PK, Integer
Name	Varchar(50)

Recipe:

Field	Type
Id	PK, Integer
Name	Varchar(50)

The relationship between the two tables is Many-to-Many, so there is a pivot table.

The filter is as follows, you have an array of recipe id, for example:

recipeIds = [1,2,3]

The current data:

Equipments:

Id	Name
100	A
101	B
102	C
103	D
104	E
105	F

Recipes:

Id	Name
1	Recipe 1
2	Recipe 2
3	Recipe 3
4	Recipe 4
7	Recipe 7
8	Recipe 8
9	Recipe 9

Pivot Table:

EquipmentId	RecipeId
100	1
100	4
101	1
101	2
103	9
103	7
104	1
104	2
104	3
105	1
105	2
105	3
105	8

Then the query should return the equipment that is present (or owns) to the recipes mentioned in recipeIds:

Query Result (Equipments):

Id	Name
104	E
105	F

The other equipments should not appear, here the reasons:

Equipment	Reason
A	Only recipe 1 is present, but not all 3 recipes mentioned
B	Only recipe 1 and 2 is present, but not all 3 recipes mentioned
C	Does not have any recipe
D	Does not have any of the mentioned recipes

I don’t know how to perform the query. I hope your help.

Answer

Try this query:

SELECT P.EquipmentID, E.Name
FROM Pivot P 
JOIN Equipments E ON P.EquipmentID=E.Id
WHERE E.RecipeId IN (1,2,3) 
GROUP BY P.EquipmentID, E.Name
HAVING COUNT(*)=3;

1. Join Pivot and Equipments table.
2. Add condition WHERE E.RecipeId IN (1,2,3).
3. Add GROUP BY P.EquipmentID, E.Name.
4. Add HAVING COUNT(*)=3; for any group of P.EquipmentID, E.Name that occur 3 times; effectively matches your condition of “only EquipmentID that appear in Receipe 1,2,3”.

Here’s a fiddle