一个复杂的SQL查询字符串内部连接公分母

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

这是一个非常重要的sql查询，我的整个网站都是基于这个查询。

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

在IngredentsTable中，我有以下几点

1. Bread
2. 面包
3. ChickenBreast
4. ChickenBreast
5. Noodles
6. 面条
7. Mayonaise
8. Mayonaise
9. Cheese
10. 奶酪
11. Ketchup
12. 番茄酱
13. Butter
14. 黄油

And the ProductsTable

和ProductsTable

1. Spageti
2. Spageti
3. Chicken Breast Sandwich
4. 鸡胸肉三明治

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——面条

Spageti --- Cheese

Spageti——奶酪

Spageti --- Ketcup

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.

我想编写一个sql查询，它获取具有指定成分的产品的id。

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

假设5是奶酪，我成功地得到了鸡胸三明治和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

如果我再加一个，MappingTable。IngredientID = 5、6;作为面包，它应该只给我一个鸡胸三明治，而不是Spageti

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

我有错误"，语法。即使是"and"也没有结果。

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

我如何检查多个成分，如地图。IngredientID = 5,6,7;

ANY HELP IS GREATLY APPRECIATED!!!

非常感谢您的帮助!!!

i need to have this in a single query..

我需要在一个查询中包含这个。

Please show me options

请给我选择

5 个解决方案

#1

With 2 queries you could use intesection of your results

通过两个查询，您可以使用您的结果的intesection。

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:

沿着这条线有两个成分id:

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;

#2

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

Sorry, my bad. How about this?:

对不起,我的坏。这个怎么样?:

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

#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.

首先，编辑您的问题，以便示例表中的数据与示例问题相匹配……如果5是奶酪，6是面包，那么让配料表与之匹配。这是令人困惑的。

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

其次，你说的是“应该只给我一个鸡胸肉三明治，而不是Spageti”，这让我觉得你想知道所有列明的食品，而不是其中任何一种。如果是，那么您需要以下内容

  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)

or, us8ng counting logic:

或者,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

#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.

如果您像其他海报建议的那样使用IN操作符，您将得到面包和奶酪的意大利面和鸡肉三明治，因为IN本质上是一个OR类型查询。

#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.

这应该是可行的，尽管您需要为它提供两个“变量”——第一个是用逗号分隔的一组配料，第二个(@ entscount)是列表中配料的数量。

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'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.

如果同一种成分可能被记录两次(尽管你的表格结构看起来不允许，也可能不需要)，那么切换两个Count(*)来计数(不同的成分组成)，并将@ componentcount改为使用的不同成分的数量。