多对多MySQL筛选器 [英] MySQL Filter on many-to-many

问题描述

我有很多表，如下表:

1)收据

-id
-name

2)配料

-id
-name

3)收据成分

-recept_id
-ingredient_id

现在，我想查找包含配料清单的收据.例如，所有包含西红柿"和比索"成分的收据. (而不是仅包含西红柿"或比索"的收据.查询的外观如何?

Now i want to find the receipts that have a list of ingredients. For example all receipts that have the ingredients 'tomato' AND 'pesto'. (And not the receipts that have only 'tomato' or 'pesto'. How will the query look like?

推荐答案

尝试一下

SELECT r.id, r.name, GROUP_CONCAT(DISTINCT(i.name)) as items
 FROM receipts r
 LEFT JOIN receipts-ingredients ri ON(ri.receipt_id = r.id)
 LEFT JOIN ingredients i ON(ri.ingredient_id = i.id)
 GROUP BY r.id
 HAVING FIND_IN_SET('tomato',items) AND FIND_IN_SET('pesto',items)

这篇关于多对多MySQL筛选器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！