The complex denominator of a SQL Query internal query string

Question

This is a very important SQL query, after which my entire site is founded.

and does not work.

It is difficult to explain without an example.

There are 2 tables, One is IngredientsTable and the other is ProductTable.

In IngredentsTable I have

• Bread
• Chickenbreast
• Noodles
• mayonnaise
• Cheese
• Ketchup
• Butter

And ProductsTable

• Spageti
• Chicken breast sandwich

And there is a MAPPING TABLE table that joins both tables. He has IngredientID and ProductID.

Now Comparison Chicken Breast Sandwich - Bread

Chicken Breast Sandwich - Mayonase

Chicken Breast Sandwich - Cheese

Chicken Breast Sandwich - Ketchup

Spaghetti --- Noodles

Spaghetti --- Cheese

Spaghetti --- Catkup

,

sql-, , .

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

, 5 ,

, WHERE MappingTable.IngredientID = 5,6; 6, ,

"," .. "" .

, WHERE MappingTable.IngredientID = 5,6,7;

!!!

.

,

Answer 1

, .

, . . , .

- 2 :

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;

Answer 2

WHERE MappingTable.IngredientID IN (5, 6, 7)

, . ?:

SELECT 
  p.id,
  p.Name 
FROM ProductTable p
INNER JOIN (SELECT * FROM MappingTable WHERE IngredientID IN (5, 6, 7)) m
  ON p.id = m.ProductID

Answer 3

, , ... 5 - , 6 - , . .

-, , " " "" ", , , ingfrediants, . ,

  Select P.id,  P.Name 
  FROM ProductTable P 
  Where Exists (Select * From Mapping Table
                Where ProductId = P.ProductId
                    And IngredientId = 5)
    And Exists (Select * From Mapping Table
                Where ProductId = P.ProductId
                    And IngredientId = 6)
    And Exists (Select * From Mapping Table
                Where ProductId = P.ProductId
                    And IngredientId = 7)

, us8ng :

   Select P.id,  P.Name 
   From ProductTable P 
   Where (Select Count(Distinct IngredientId)
          From MappingTable M
          Where ProductId = P.ProductId 
            And IngredientId In (5,6,7)) = 3

Answer 4

:

SELECT 
  ProductTable.id,
  ProductTable.Name 
FROM ProductTable 
INNER JOIN MappingTable AS MappingTable1
  ON ProductTable.id = MappingTable1.ProductID
  AND MappingTable1.IngredientID = 5
INNER JOIN MappingTable AS MappingTable2
  ON ProductTable.id = MappingTable2.ProductID
  AND MappingTable2.IngredientID = 6

IN, , , , IN OR.

Answer 5

This should work, although you need to provide two “variables”: the first is a set of Ingredients separated by commas, the second (@IngredientsCount) is the number of ingredients in this list.

SELECT ProductsTable.id, ProductTable.Name
FROM ProductsTable
INNER JOIN (SELECT ProductID, Count(*) AS Ingredients
    FROM MappingTable
    WHERE IngredientID IN (...ids of ingredients here...)
    GROUP BY ProductID
    HAVING Count(*) = @IngredientsCount) AS ProductIngredients ON ProductsTable.ProductID = ProductIngredients.ProductID

If it is possible that the same ingredient will be recorded twice (although your table structure does not think this allows, and this is probably not necessary), switch the two Count (*) to Count (Distinct IngredientID) and change @IngredientCount - the amount of different ingredients used.