A complex SQL Query string inner join common denominator

Question

This is a very very important sql query after which my whole website is based..

and its not working..

Its difficult to explain without an example..

There are 2 tables, One is IngredientsTable and other one is ProductsTable.

In IngredentsTable i have the following

1. Bread
2. ChickenBreast
3. Noodles
4. Mayonaise
5. Cheese
6. Ketchup
7. Butter

And the ProductsTable

1. Spageti
2. Chicken Breast Sandwich

And there is a MAPPING TABLE that connects both tables. It has IngredientID and ProductID

Now, Mapping table Chicken Breast Sandwich - Bread

Chicken Breast Sandwich - Mayonase

Chicken Breast Sandwich - Cheese

Chicken Breast Sandwich - Ketchup

Spageti --- Noodles

Spageti --- Cheese

Spageti --- Ketcup

You'll notice that Cheese and Ketchup are common entries to both Chicken Breast And Spageti

I want to write an sql query that gets the IDs OF PRODUCTS THAT HAVE THE SPECIFIED INGREDIENTS.

I'm able to achieve it partially with the following query

SELECT 
  ProductTable.id,
  ProductTable.Name 
FROM ProductTable 
INNER JOIN MappingTable 
  ON ProductTable.id = MappingTable.ProductID
WHERE MappingTable.IngredientID =  5; 

Suppose 5 was cheese, I'm successfully able to get results of Chicken Breast Sandwich and Spageti

But If i add One more, WHERE MappingTable.IngredientID = 5,6; 6 being Bread, it should only show me an Chicken Breast Sandwich and NOT Spageti

I'm getting Error "," syntax.. even "and" is not getting results.

How can I check multiple Ingredients like WHERE MappingTable.IngredientID = 5,6,7;

ANY HELP IS GREATLY APPRECIATED!!!

i need to have this in a single query..

Please show me options

Answer 1

With 2 queries you could use intesection of your results

But you say that you want it in one query.

A close approximation would be to have group by statement and count amount of rows received from your result. It must be equal to amount of your ingredients. It will not work if your ingredients repeat more than once in the same product though.

something along this line for 2 Ingredient IDs:

SELECT ProductTable.id, ProductTable.Name FROM ProductTable 
INNER JOIN MappingTable ON ProductTable.id = MappingTable.ProductID 
WHERE MappingTable.IngredientID in (5,6) group by ProductTable.id, ProductTable.Name 
HAVING count(*) = 2;