复杂的SQL查询字符串内部联接共同点 [英] A complex SQL Query string inner join common denominator

问题描述

这是一个非常重要的sql查询，其基础是我的整个网站.

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

它不起作用..

没有例子很难解释.

有2个表，一个是IngredientsTable，另一个是ProductsTable.

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

在 IngredentsTable 中，我具有以下内容

1. 面包
2. ChickenBreast
3. 面条
4. 蛋黄酱
5. 奶酪
6. 番茄酱
7. 黄油

和 ProductsTable

1. Spageti
2. 鸡胸肉三明治

并且有一个MAPPING TABLE连接两个表.它具有IngredientIDID和ProductID

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

现在，映射表鸡胸肉三明治-面包

Now, Mapping table Chicken Breast Sandwich - Bread

鸡胸肉三明治-蛋黄酱

鸡胸肉三明治-奶酪

鸡胸肉三明治-番茄酱

Spageti ---面条

Spageti --- Noodles

Spageti ---奶酪

Spageti --- Cheese

Spageti --- Ketcup

Spageti --- Ketcup

您会注意到，奶酪和番茄酱是鸡胸肉和意粉酱的常见条目

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

我想编写一个SQL查询，以获取具有指定成分的产品ID.

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; 

假设5是奶酪，我可以成功地获得鸡胸肉三明治"和斯佩吉蒂"的结果

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

但是如果我再添加一个， WHERE MappingTable.IngredientID = 5,6; 6是面包，它应该只显示我的鸡胸肉三明治而不是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.

如何检查多个成分，例如 WHERE MappingTable.IngredientID = 5,6,7;

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

任何帮助都非常感谢！

我需要在单个查询中添加它.

i need to have this in a single query..

请向我显示选项

推荐答案

通过2个查询，您可以对结果进行积分

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.

此行中包含2个成分ID的内容:

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;

这篇关于复杂的SQL查询字符串内部联接共同点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！