MySQL +查询返回在另一个表中具有col处于活动状态的col的所有行 [英] MySQL + Query to return all rows that have cols that are 'active' in another table
问题描述
我正在寻找有关如何进行此查询的帮助..以及如何高效地进行查询.
I am looking for some help on how to go about this query.. and also about doing in an efficient way as well.
我(不是)对MySQL/查询有经验(我的技能在JOIN级别上变得阴云密布！..大声笑)..
I am -not- that experienced with MySQL/queries (my skills get cloudy at about the JOIN level!.. lol)..
因此，我将牺牲一些我实际上也了解的可读取代码/查询的效率. :)
So I will sacrifice some efficiency for -readable- code/query that I actually understand as well. :)
我还有一个示例Rextester设置供您查看:
I have an example Rextester set-up for you to look at as well:
http://rextester.com/FWVO47690
-
我知道当前有很多列..(其中一些会消失)..关键列是与饮料和成分表匹配的Ingredient_code.
I know there is a lot of columns currently.. (some will be going away).. the key column is the ingredient_code that matches the drink and ingredients table.
对于酒水桌..我可以像在桌子上那样勾勒出轮廓...
For the drinks table.. I can do it like how I have it outlines there...
或者我只能有一个成分列...但是数据会像这样:
or I can have just one ingredients column... but the data would be like:
将从饮料菜单界面中发送哪些动作(序列)数据..如果这样可以更轻松地使用，而不是将每种成分，位置和数量分为可能的15种单独的列呢?
Which is the action (serial) data that will get sent out from the drink menu interface.. if that makes it easier to use instead of separating each ingredients, position and amount into separate columns for all possibly 15?
摘要: 我正在创建一个前端界面(网页)，该界面托管在安装了LAMP(或多或少)的Raspberry Pi上.
Summary: I am creating a front end interface (webpage).. that is hosted on a Raspberry Pi with LAMP installed (more or less)..
这是饮料菜单.= 饮料桌"或多或少是所有饮料的图书馆" ...
This is a drink menu.= The 'drink table' is more or less a 'library' of all drinks...
加载页面"时..我想运行/执行查询以返回饮料表中的所有饮料..在另一个表中具有匹配和有效"成分. (希望这样吗?)
When the 'page' loads.. I want to run/execute a query to return all drinks from the drink table.. that have a 'matching and active' ingredient in another table. (hope that makes sense?)
所以根据成分表，饮料菜单有点动态".
So the drink menu is sort of 'dynamic' based on the ingredients table..
如果需要喝酒，
橙汁和伏特加酒..
如果在配料表中橙汁或伏特加酒未激活，则该菜单将不会激活或在饮料菜单中显示.等.
It will not be active or display in the drink menu if in the ingredients table orange juice or vodka is not active...etc..
**每种饮料可以包含1到15种成分...
** Each drink can have from 1 to 15 ingredients...
所以我不确定如何通过查询执行此操作?我确定是否需要一些联接和子查询...
So I'm not sure how to go about doing this with a query? I'm sure if will need some joins and sub queries...
所以我正在寻找一些指导(至少关于要搜索的关键字..等)
So I'm looking for some guidance (at least on what key words to search on..etc)
我觉得这不是很有效吗? (虽然我不必太担心速度"，因为它在每个电源循环后只需要加载一次....我不希望它落后或出现任何问题..大声笑)
I feel like this isnt going to be very efficient? (while I'm not worried too much about 'speed' since it only needs to load once upon each power-cycle.... I dont want it to lag out or anything..LOL)
我尝试了这种尝试..但是这不允许在饮料中添加多种成分..我只做了"1"..
I fudged together this attempt.. but this doesnt allow for multiple ingredients in a drink.. I only did '1'..
SELECT * FROM barbot_drinks AS drinks
WHERE EXISTS (
SELECT * FROM barbot_ingredients AS ingredients
WHERE drinks.ingredient_1_code = ingredients.ingredient_code
)
像这样的东西会带来误报..因为其中一种成分可能会丢失..但是前一种或后一种成分会匹配" ..因此它会被退回(当它不应该使用时)
Something like this give false positives.. because one of the ingredients could be missing.. but a previous or later one does 'match'.. hence it gets returned (when it shouldnt)
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
);
有关尼克斯提供的解决方案的更新:
Update on Nicks provided solution:
**正在寻找更新，因为如果您更改表，则通过删除(比如说)Ingredient_x_dispenser值(或_code或_dosage)..但保留其他值仍然返回..
** Was looking for an update because if you alter the table, by removing (say) the ingredient_x_dispenser value (or _code or _dosage).. but leave the other it still gets returned..
对于每个饮料行，每个#成分都必须具有所有3个字段(_dispenser，_code，_dosage)！=''...&&在另一个表&中也具有该成分代码活动).如果无法正确传达此信息，对不起.
for each drink row, each # ingredient needs to have ALL 3 fields (_dispenser, _code, _dosage) != ''... && also have that ingredient_code in the other table & active).. sorry if I am not relaying this correctly.
@尼克(这样吗?)
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));
我觉得应该更像这样:(但这返回零行)
I feel like it should be more like this: (but this returns zero rows)
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));
重述:
我只想从酒水表中退回酒水:
I only want to return drinks, from the drinks table that:
-
如果Ingredient_x_dispenser中存在一个值，则-or- Ingredient_x_code，-or- Ingredient_x_dosage ..表示 x 的所有'3'值都存在.(_ dispenser，_code，_dosage为了讨论的目的，在我的脑海中补上1个成分"，每个配方(行)可以有1-15个成分"，希望这是有道理的.)
If there is a value in ingredient_x_dispenser, -or- ingredient_x_code, -or- ingredient_x_dosage.. that all '3' value for x are present.. (_dispenser, _code, _dosage make up 1 'ingredient' in my mind for discussion purposes, and there can be 1 - 15 'ingredients' per recipe (row), hope that makes sense)
如果在饮料行中满足了-3-成分要求，则还必须在其他成分表中找到"ACTIVE"并具有匹配的_CODE(如现在一样).它遵循的标准的第一部分
if in the drinks rows there is a -3- ingredient requirement met, than it also has to be found in the other ingredients table as ACTIVE and with the matching _CODE, (as it does now).. if the first part of the criteria that its getting by on
推荐答案
我将首先说您确实需要规范化数据库.但这是另一个问题.同时，您可以使用此查询.我只显示了四种成分，您需要根据情况将其扩展到15种.
I'll start by saying you really need to normalise your database. But that is another question. In the meantime, you can use this query. I've only shown it for four ingredients, you would need to extend it to 15 for your case.
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))
使用您的样本数据，将产生以下输出:
With your sample data, this produces the following output:
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 .
Based on somewhat extended discussions with OP, the query has been refined as below. This query requires that dispenser, code and dosage are all present for each ingredient in the drink. An SQLFiddle is available.
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 +查询返回在另一个表中具有col处于活动状态的col的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
