A Complex Sql Query String Inner Join Common Denominator

June 30, 2023 Post a Comment

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 Ingre

Solution 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 
INNERJOIN MappingTable ON ProductTable.id = MappingTable.ProductID 
WHERE MappingTable.IngredientID in (5,6) groupby ProductTable.id, ProductTable.Name 
HAVINGcount(*) =2;

Solution 2:

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

Sorry, my bad. How about this?:

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

Solution 3:

First of all, edit your question so that the data in your example tables matches the example question... If 5 is cheese and 6 is Bread, then make the Ingredients table match that. it's confusing otherwise.

Secondly, you're stating that ", it should only show me an Chicken Breast Sandwich and NOT Spageti" makes me think you want to know the products that have ALL the listed ingfrediants, not ANY of them. If so then you want the following

Select P.id,  P.Name 
  FROM ProductTable P 
  WhereExists (Select*From Mapping TableWhere ProductId = P.ProductId
                    And IngredientId =5)
    AndExists (Select*From Mapping TableWhere ProductId = P.ProductId
                    And IngredientId =6)
    AndExists (Select*From Mapping TableWhere ProductId = P.ProductId
                    And IngredientId =7)

or, us8ng counting logic:

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

Solution 4:

You need to link to the mapping table individually for each ingredient:

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

If you use the IN operator as other posters have suggested, you will get both spaghetti and chicken sandwich for bread and cheese because IN is inherently an OR type query.

Solution 5:

This should work, although you need to supply two 'variables' to it - the first is a comma-delimited set of IngredientIDs, the second (@IngredientsCount) is the number of ingredients in that list.

SELECT ProductsTable.id, ProductTable.Name
FROM ProductsTable
INNERJOIN (SELECT ProductID, Count(*) AS Ingredients
    FROM MappingTable
    WHERE IngredientID IN (...ids of ingredients here...)
    GROUPBY ProductID
    HAVINGCount(*) =@IngredientsCount) AS ProductIngredients ON ProductsTable.ProductID = ProductIngredients.ProductID

If it's possible for the same ingredient to be recorded twice (although your table structure doesn't look it allows for that, and it's probably not necessary), switch the two Count(*) to Count(Distinct IngredientID) and change @IngredientCount to be the number of different ingredients used.

Lab Sql