如何选择具有两个不同属性的一个列的行 - How to select a row which has one coloumn with two different attributes?

How to select a row which has one coloumn with two different attributes?

我正在做一个关于用PHP（使用Codeigniter）和MYSQL烹饪食谱的项目。

我有三张桌子：

Ingredients-id、name
Recipe-id，name
ing_to_rep-recipe_id，ingredient_id（我用这个表来保存哪个配方有哪些成分。）

"获取所有有鸡蛋（id = 64）和盐（id = 65）的食谱"的查询是什么

我试过了：

SELECT * FROM recipe JOIN ing_to_rep ON recipe.id = ing_to_rep.rep_id 
WHERE ing_to_rep.ing_id = 64 AND ing_to_rep.ing_id = 65

当然，它什么也不返回，但它可以帮助你完成我要做的事情。

可能有一种更高效、更灵活的方法，但有两个子查询联接可以：

SELECT
  recipe.*
FROM recipe
  JOIN (SELECT recipe_id FROM ing_to_rep WHERE ingredient_id = 64) AS ing1 ON recipe.id = ing1.recipe_id
  JOIN (SELECT recipe_id FROM ing_to_rep WHERE ingredient_id = 65) AS ing2 ON recipe.id = ing2.recipe_id

也可以使用EXISTS

SELECT 
  recipe.*
FROM 
  recipe
WHERE
  EXISTS (SELECT recipe_id FROM ing_to_rep ing1 WHERE ingredient_id = 64 AND recipe.id = ing1.recipe_id)
  AND  EXISTS (SELECT recipe_id FROM ing_to_rep ing2 WHERE ingredient_id = 65 AND recipe.id = ing2.recipe_id)

另一种解决方案是类似

SELECT * FROM recipe r JOIN 
(SELECT recipe_id FROM ing_to_rep 
 GROUP BY recipe_id
 HAVING SUM(IF(ingredient_id IN (64, 65), 1, 0)) = 2) gr ON r.id = gr.recipe_id

我对表演不太确定，你得自己试试。

无子查询的方法（CI的AR不支持子查询）：

SELECT *
FROM recipe
JOIN ing_to_rep ON (ing_to_rep.id_recipe=recipe.id AND (ing_to_rep.ing_id=64 OR ing_to_rep.ing_id=65))
GROUP BY recipe.id
HAVING COUNT(ing_to_rep.ing_id)=2

您可以使用此解决方案CI的ActiveRecord。