如何根据整数 (ID) 数组仅返回满足所有条件的行? [英] How do I return only rows where all conditions are met based on an array of intergers (IDs)?

问题描述

从页面 - MyBarCabinet - 我可以选择可用的原材料.这些原材料(只有 ID)被放入一个数组并存储在 cookie 中.

From a page - MyBarCabinet - I can select what raw materials I have available. These raw materials (only the ID's) is put into an array and stored in a cookie.

现在；我的目标是只获得不需要比饼干中存储的更多/其他原材料的饮料.换句话说;如果我检查过我只有16"、34"和35"可用:只有只需要这些原料的饮料才会出现，而不是那些除了其他原料之外还含有这些原料的饮料..

Now; my goal is to only get the drinks that do not require any more/other raw materials than what is stored in the cookie. In other words; If I've checked that I only have "16", "34" and "35" available: only the drinks that only require these raw materials should show up, and not those drinks that consists of these in addition to other raw materials..

我从 MySQL 数据库中查询饮料和所有内容.这部分由三个表组成:drinks_recipes、drink_recipes_ingredients 和 raw_materials.
drink_recipes_ingredients 表用于将饮料配方与原材料链接"起来.

I query drinks, and all that, from a MySQL database. This part is build up of three tables: drinks_recipes, drink_recipes_ingredients and raw_materials.
The drink_recipes_ingredients-table is used to "link" the drink recipes with the raw materials.

如果我这样做:

SELECT 
    dr.id drink_id, dr.name drink_name,
    rm.name ingredient_name
FROM drink_recipes_ingredients dri
    JOIN drink_recipes dr ON dr.id = dri.fk_recipes_id
    JOIN raw_materials rm ON rm.id = dri.fk_raw_materials_id
WHERE rm.id IN (16,34,35)

这正是我不想要的.我要购买的所有饮料都至少含有一种选定的原材料.

This does exactly what I don't want. I'm getting all drinks that contains at least one of the selected raw materials.

我确实看过这篇文章:仅当满足多个子句时如何返回一行，但我无法弄清楚如何在我的情况下做类似的事情.但目标看起来是一样的.

I did have a look at this post: How to return a row only if multiple clauses are met, but I couldn't figure out how to do something similar in my case. But the goal looks like to be the same.

推荐答案

Baramar 似乎有一个有趣的解决方案，但对于此类问题我通常使用找到相反"的方法.我们首先查找与您的清单不符的所有饮品.然后我们选择不是这些的饮料:

Baramar seems to have an interesting solution, but for this kind of problem I usually use the "find the opposite" method. We start by finding all the drinks that DON'T match your list. Then we select drinks that aren't those:

SELECT *
FROM drink_recipes
WHERE drink_recipes.id NOT IN (    
    SELECT 
        dr.id 
    FROM drink_recipes_ingredients dri
        JOIN drink_recipes dr ON dr.id = dri.fk_recipes_id
        JOIN raw_materials rm ON rm.id = dri.fk_raw_materials_id
    WHERE rm.id NOT IN (16,34,35)
)

这篇关于如何根据整数 (ID) 数组仅返回满足所有条件的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！