mysql+query返回在另一个表中具有“active”列的所有行

我正在寻求有关如何进行此查询的帮助。。同时也要有效率地去做。
我没有mysql/查询方面的经验（我的技能在join级别上有些模糊！）！。。哈哈。。
因此，我将牺牲一些效率来换取我真正理解的可读代码/查询。：）
我也有一个rextester设置示例供您参考：
http://rextester.com/fwvo47690
我知道现在有很多专栏(有些人会离开。。键列是与饮料和配料表匹配的配料代码。
在酒桌上。。我可以这样做，就像我有它在那里。。。
或者我可以有一个成分栏。。。但是数据会是这样的：
这是将从饮料菜单界面发出的动作（串行）数据。。如果这使得它更容易使用，而不是分离每个成分，位置和数量到所有可能的15个单独的列？
摘要：我正在创建一个前端界面（网页）。。那是在一个树莓皮与灯安装（或多或少）。。
这是一份饮料菜单。=饮料桌或多或少是所有饮料的“图书馆”。。。
当“页面”加载时。。我想运行/执行一个查询以返回饮料表中的所有饮料。。在另一个表中有“匹配和活性”成分的(希望这有意义？）
所以饮料菜单是基于配料表的“动态”菜单。。
如果饮料需要：
橙汁和伏特加。。
如果配料表中的橙汁或伏特加不起作用，它将不起作用或显示在饮料菜单中。。

**每种饮料可以有1到15种成分。。。

所以我不知道如何用查询来完成这个任务？我确定如果需要一些连接和子查询。。。
所以我在寻找一些指导（至少在搜索什么关键词等方面）
我觉得这样效率不高(虽然我不太担心'速度'，因为它只需要在每个电源周期加载一次。。。。我不想它落后或什么..哈哈）
我捏造了这次尝试。。但这不允许饮料中含有多种成分。。我只做了“1”。。

SELECT * FROM barbot_drinks AS drinks
WHERE EXISTS (
  SELECT * FROM barbot_ingredients AS ingredients 
  WHERE drinks.ingredient_1_code = ingredients.ingredient_code
)

像这样的东西会导致误报。。因为其中一种成分可能不见了。。但前一个或后一个确实“匹配”。。因此，它得到了回报（当它不应该）

SELECT * FROM barbot_drinks AS drinks
WHERE EXISTS(
  SELECT * FROM barbot_ingredients AS ingredients    
  WHERE drinks.ingredient_1_code = ingredients.ingredient_code 
    OR drinks.ingredient_2_code = ingredients.ingredient_code 
    OR drinks.ingredient_3_code = ingredients.ingredient_code 
    OR drinks.ingredient_4_code = ingredients.ingredient_code 
    OR drinks.ingredient_5_code = ingredients.ingredient_code 
    OR drinks.ingredient_6_code = ingredients.ingredient_code 
    OR drinks.ingredient_7_code = ingredients.ingredient_code 
    OR drinks.ingredient_8_code = ingredients.ingredient_code 
    OR drinks.ingredient_9_code = ingredients.ingredient_code 
    OR drinks.ingredient_10_code = ingredients.ingredient_code 
    OR drinks.ingredient_11_code = ingredients.ingredient_code 
    OR drinks.ingredient_12_code = ingredients.ingredient_code 
    OR drinks.ingredient_13_code = ingredients.ingredient_code 
    OR drinks.ingredient_14_code = ingredients.ingredient_code 
    OR drinks.ingredient_15_code = ingredients.ingredient_code 

);

更新nicks提供的解决方案：

**正在寻找更新，因为如果您更改表，通过删除（例如）配料分配值（或代码或剂量）。。但是离开另一个它仍然会被退回。。

对于每个饮料行，每个#配料都需要有所有3个字段（#分配器、#代码、#剂量）！=''…&&在另一个表中也有该成分的代码（&active）。。对不起，如果我没有转达这个正确。
@尼克（像这样？）

SELECT * 
FROM barbot_drinks d
WHERE (ingredient_1_dispenser = '' AND ingredient_1_code = '' AND ingredient_1_dosage = '' OR 
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_1_code)) AND
    (ingredient_2_dispenser = '' AND ingredient_2_code = '' AND ingredient_2_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_2_code)) AND
    (ingredient_3_dispenser = '' AND ingredient_3_code = '' AND ingredient_3_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_3_code)) AND
    (ingredient_4_dispenser = '' AND ingredient_4_code = '' AND ingredient_4_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_4_code)) AND
    (ingredient_5_dispenser = '' AND ingredient_5_code = '' AND ingredient_5_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_5_code)) AND
    (ingredient_6_dispenser = '' AND ingredient_6_code = '' AND ingredient_6_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_6_code)) AND
    (ingredient_7_dispenser = '' AND ingredient_7_code = '' AND ingredient_7_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_7_code)) AND
    (ingredient_8_dispenser = '' AND ingredient_8_code = '' AND ingredient_8_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_8_code)) AND
    (ingredient_9_dispenser = '' AND ingredient_9_code = '' AND ingredient_9_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_9_code)) AND
    (ingredient_10_dispenser = '' AND ingredient_10_code = '' AND ingredient_10_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_10_code)) AND
    (ingredient_11_dispenser = '' AND ingredient_11_code = '' AND ingredient_11_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_11_code)) AND
    (ingredient_12_dispenser = '' AND ingredient_12_code = '' AND ingredient_12_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_12_code)) AND
    (ingredient_13_dispenser = '' AND ingredient_13_code = '' AND ingredient_13_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_13_code)) AND
    (ingredient_14_dispenser = '' AND ingredient_14_code = '' AND ingredient_14_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_14_code)) AND
    (ingredient_15_dispenser = '' AND ingredient_15_code = '' AND ingredient_15_dosage = '' OR
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_15_code));

我觉得应该是这样的：（但是这个返回零行）

SELECT * 
FROM barbot_drinks d
WHERE (ingredient_1_dispenser != '' AND ingredient_1_code != '' AND ingredient_1_dosage != '' AND 
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_1_code)) AND
    (ingredient_2_dispenser != '' AND ingredient_2_code != '' AND ingredient_2_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_2_code)) AND
    (ingredient_3_dispenser != '' AND ingredient_3_code != '' AND ingredient_3_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_3_code)) AND
    (ingredient_4_dispenser != '' AND ingredient_4_code != '' AND ingredient_4_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_4_code)) AND
    (ingredient_5_dispenser != '' AND ingredient_5_code != '' AND ingredient_5_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_5_code)) AND
    (ingredient_6_dispenser != '' AND ingredient_6_code != '' AND ingredient_6_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_6_code)) AND
    (ingredient_7_dispenser != '' AND ingredient_7_code != '' AND ingredient_7_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_7_code)) AND
    (ingredient_8_dispenser != '' AND ingredient_8_code != '' AND ingredient_8_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_8_code)) AND
    (ingredient_9_dispenser != '' AND ingredient_9_code != '' AND ingredient_9_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_9_code)) AND
    (ingredient_10_dispenser != '' AND ingredient_10_code != '' AND ingredient_10_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_10_code)) AND
    (ingredient_11_dispenser != '' AND ingredient_11_code != '' AND ingredient_11_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_11_code)) AND
    (ingredient_12_dispenser != '' AND ingredient_12_code != '' AND ingredient_12_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_12_code)) AND
    (ingredient_13_dispenser != '' AND ingredient_13_code != '' AND ingredient_13_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_13_code)) AND
    (ingredient_14_dispenser != '' AND ingredient_14_code != '' AND ingredient_14_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_14_code)) AND
    (ingredient_15_dispenser != '' AND ingredient_15_code != '' AND ingredient_15_dosage != '' AND
    EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.ingredient_code = d.ingredient_15_code));

重新封顶：
我只想归还饮料，从饮料桌上：
如果配料分配器中有值，-或-配料编码，-或-配料用量。。x的所有“3”值都存在(_分配器，\代码，\剂量在我的脑海中组成了一个'成分'供讨论之用，每个配方（行）可以有1-15个'成分'，希望有意义）
如果在饮料行中有一个-3-成分的要求得到满足，那么它也必须在其他成分表中被发现是有效的，并且带有匹配的代码（就像现在一样）。。如果第一部分的标准，这是通过

首先我要说的是你真的需要让你的数据库正常化。但这是另一个问题。同时，您可以使用此查询。我只展示了四种成分，你需要把它扩展到15种。

SELECT * 
FROM barbot_drinks d
WHERE (ingredient_1_dispenser = '' OR 
       EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_1_dispenser AND i.ingredient_code = d.ingredient_1_code)) AND
      (ingredient_2_dispenser = '' OR
       EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_2_dispenser AND i.ingredient_code = d.ingredient_2_code)) AND
      (ingredient_3_dispenser = '' OR
       EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_3_dispenser AND i.ingredient_code = d.ingredient_3_code)) AND
      (ingredient_4_dispenser = '' OR
       EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_4_dispenser AND i.ingredient_code = d.ingredient_4_code))

对于示例数据，这将生成以下输出：

id  drink_id    drink_name   drink_image      drink_desc                    ingredient_1_dispenser  ingredient_1_code   ingredient_1_dosage     ingredient_2_dispenser  ingredient_2_code   ingredient_2_dosage     ingredient_3_dispenser  ingredient_3_code   ingredient_3_dosage     ingredient_4_dispenser  ingredient_4_code   ingredient_4_dosage
1   vdk_org     Screw Driver screw_driver.jpg Screw Driver description...   bottle  vdk     1   valve   oj  2000                                                                                                                                                            
2   vdk_cran    Cape Cod     cape_cod.jpg     Cape Cod description...       bottle  vdk     1   valve   cbj     2000                                                                                                                                                            
3   dry_mrtn    Dry Martini  dry_martini.jpg  Dry Martini description...    bottle  vdk     2.5     bottle  vrmth   .5  valve   orgbit  200

编辑
基于与op的一些扩展讨论，该查询已细化如下。此查询要求饮料中的每种成分都有分配器、代码和剂量。sqlfiddle可用。

SELECT * 
FROM barbot_drinks d
WHERE (ingredient_1_dispenser = '' AND ingredient_1_code = '' AND ingredient_1_dosage = '' OR
       ingredient_1_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_1_dispenser AND i.ingredient_code = d.ingredient_1_code)) AND
      (ingredient_2_dispenser = '' AND ingredient_2_code = '' AND ingredient_2_dosage = '' OR
       ingredient_2_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_2_dispenser AND i.ingredient_code = d.ingredient_2_code)) AND
      (ingredient_3_dispenser = '' AND ingredient_3_code = '' AND ingredient_3_dosage = '' OR
       ingredient_3_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_3_dispenser AND i.ingredient_code = d.ingredient_3_code)) AND
      (ingredient_4_dispenser = '' AND ingredient_4_code = '' AND ingredient_4_dosage = '' OR
       ingredient_4_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_4_dispenser AND i.ingredient_code = d.ingredient_4_code)) AND
      (ingredient_5_dispenser = '' AND ingredient_5_code = '' AND ingredient_5_dosage = '' OR
       ingredient_5_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_5_dispenser AND i.ingredient_code = d.ingredient_5_code)) AND
      (ingredient_6_dispenser = '' AND ingredient_6_code = '' AND ingredient_6_dosage = '' OR
       ingredient_6_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_6_dispenser AND i.ingredient_code = d.ingredient_6_code)) AND
      (ingredient_7_dispenser = '' AND ingredient_7_code = '' AND ingredient_7_dosage = '' OR
       ingredient_7_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_7_dispenser AND i.ingredient_code = d.ingredient_7_code)) AND
      (ingredient_8_dispenser = '' AND ingredient_8_code = '' AND ingredient_8_dosage = '' OR
       ingredient_8_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_8_dispenser AND i.ingredient_code = d.ingredient_8_code)) AND
      (ingredient_9_dispenser = '' AND ingredient_9_code = '' AND ingredient_9_dosage = '' OR
       ingredient_9_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_9_dispenser AND i.ingredient_code = d.ingredient_9_code)) AND
      (ingredient_10_dispenser = '' AND ingredient_10_code = '' AND ingredient_10_dosage = '' OR
       ingredient_10_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_10_dispenser AND i.ingredient_code = d.ingredient_10_code)) AND
      (ingredient_11_dispenser = '' AND ingredient_11_code = '' AND ingredient_11_dosage = '' OR
       ingredient_11_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_11_dispenser AND i.ingredient_code = d.ingredient_11_code)) AND
      (ingredient_12_dispenser = '' AND ingredient_12_code = '' AND ingredient_12_dosage = '' OR
       ingredient_12_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_12_dispenser AND i.ingredient_code = d.ingredient_12_code)) AND
      (ingredient_13_dispenser = '' AND ingredient_13_code = '' AND ingredient_13_dosage = '' OR
       ingredient_13_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_13_dispenser AND i.ingredient_code = d.ingredient_13_code)) AND
      (ingredient_14_dispenser = '' AND ingredient_14_code = '' AND ingredient_14_dosage = '' OR
       ingredient_14_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_14_dispenser AND i.ingredient_code = d.ingredient_14_code)) AND
      (ingredient_15_dispenser = '' AND ingredient_15_code = '' AND ingredient_15_dosage = '' OR
       ingredient_15_dosage != '' AND EXISTS(SELECT * FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_15_dispenser AND i.ingredient_code = d.ingredient_15_code))

mysql+query返回在另一个表中具有“active”列的所有行

1条答案

相关问题

热门标签

最新问答