MySQL + 调整当前查询以从另一个表中获取额外数据(每列) [英] MySQL + Adjusting current query to get additional data from another table (per column)
问题描述
这是来自上一个线程的新问题/更新.MySQL + 查询以返回在另一个表中具有活动"列的所有行
This a new question/update from a previous thread. MySQL + Query to return all rows that have cols that are 'active' in another table
感谢成员@Nick 的帮助，我正在使用 PHP 和 MySQL 通过查询返回一些数据
I am using PHP and MySQL to return some data with a query thank to the help of member @Nick
我目前需要帮助调整当前查询，以某种方式从当前返回的行/数据中的另一个表返回替代数据.
I currently need help in tweaking the current query to somehow return substitute data from another table in the rows/data it is currently returning.
以下是表设置的一些当前示例以及当前查询状态:
Here are some current examples of what the table set-up is like, and the current query state:
雷克斯特:http://rextester.com/RZXI72814
SQL 小提琴:http://www.sqlfiddle.com/#!9/296b5/3
它目前按照我在上一个请求中概述的方式工作，但是一旦我使用它，我意识到我错过了一个要求，事后看来我觉得这是相当重要的......大声笑(抱歉)
It currently works as I outlined in the previous request, but once I put it to use, I realized I missed a requirement, which in hindsight I feel is fairly significant...LOL (sorry)
当前状态:(按预期工作)查询返回barbot_drinks"表中的所有行[具有 (1-15) 个成分，由(每个 3 列)_dispenser# _code# &_dosage#].. 在 barbot_ingredients 表中找到并且处于活动状态.
Current State: (working as expected) Query that returns all rows from the 'barbot_drinks' table [that has (1-15) ingredients made up of (3 cols each) _dispenser# _code# & _dosage#].. that are EACH found in the barbot_ingredients table AND are active.
如果任何 _dispenser# _code# &_dosage# cols 为空，整个成分"无效...空字段不计算...通常表示饮料配方的结束(一杯饮料最多只能有 15 种成分)
If any of the _dispenser# _code# & _dosage# cols are empty, the whole 'ingredient' is invalid...and empty fields dont count...usually signify the end of the drink recipe (a drink can only have a max of 15 ingredients)
示例:如果饮料配方/条目只有 2 个成分"(ingredient_1_dispenser、成分_1_code 和成分_1_剂量和成分_2_分配器、成分_2_代码和成分_2_剂量)
example: if a drink recipe/entry only has 2 'ingredients' (ingredient_1_dispenser, ingredient_1_code, and ingredient_1_dosage & ingredient_2_dispenser, ingredient_2_code, and ingredient_2_dosage)
伏特加&OJ
它检查成分表以查看 barbot_ingredients 表中是否存在 VODKA 和 OJ 条目并且是否处于活动状态.
it checks the ingredients table to see if both VODKA and OJ entries exist in the barbot_ingredients table AND are active.
这就是我们现在所处的位置.
So thats where we are now.
我需要尝试做的是从 barbot_ingredients 中获取 -EACH-成分_x_code 列的 'dispenser_order' 值(每杯饮料可能是 1-15 份).对于返回的每杯饮料.(即:该特定饮料的所有成分都可以在成分表中找到并且是有效的)
What I need to try and do is get the 'dispenser_order' value from the barbot_ingredients for -EACH- ingredient_x_code column (which could be 1-15 per drink)..for each drink that gets returned. (ie: all ingredients for that paticular drink are found in the ingredients table and are active)
我正在解析每一行并使用 PHP 从该查询的结果集中创建一个字符串.
I am parsing each row and creating a string using PHP from the result set from this query.
我目前正在创建的内容:
what I am currently creating:
示例(螺旋驱动饮料):(被php脚本解析后)
Example (Screw Drive Drink): (after being parsed by php script)
bottle=vdk:1,valve=oj:2000
bottle=vdk:1,valve=oj:2000
或(列名)
[ingredient_1_dispenser]=[ingredient_1_code]:[ingredient_1_dosage],[ingredient_2_dispenser]=[ingredient_2_code]:[ingredient_2_dosage]
[ingredient_1_dispenser]=[ingredient_1_code]:[ingredient_1_dosage],[ingredient_2_dispenser]=[ingredient_2_code]:[ingredient_2_dosage]
我需要构建的是:
瓶子=1:1，阀门=1:2000
bottle=1:1,valve=1:2000
或
[ingredient_1_dispenser]=[###dispenser_order - 与此设置匹配的 FROM_INGREDIENT 表 - =成分_1_code###]:[ingredient_1_dosage],[ingredient_2_dispenser]=[###dispenser_order- 与此设置匹配的 FROM_INGREDIENT 表 - =成分_2_代码###]:[ingredient_2_dosage]
[ingredient_1_dispenser]=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_1_code###]:[ingredient_1_dosage],[ingredient_2_dispenser]=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_2_code###]:[ingredient_2_dosage]
ie:(被php脚本解析后)
ie: (after being parsed by php script)
bottle=[###dispenser_order - 与此设置匹配的 FROM_INGREDIENT 表 - = 成分_1_代码###]:1,
bottle=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_1_code###]:1,
valve=[###dispenser_order - 与此设置匹配的 FROM_INGREDIENT 表 - = 原料_2_代码###]:2000
valve=[###dispenser_order - FROM_INGREDIENT TABLE THAT MATCHES THIS SETS - = ingredient_2_code###]:2000
由于 1-15 个可能的列组合，当前很长，但我也要求一些易于阅读的内容，因此我也可以理解并从中学习..(就像我到目前为止一样！)..
The current is long because of the 1-15 possible column combination, but I also asked for something was easily readable so I can also understand and learn from it.. (like I have so far!)..
如果我可以提供任何其他信息，或者我有什么不清楚的地方，请告诉我.我想提供完整的背景、工作代码的链接以及我正在/正在做什么以及我想要/做什么的示例.
Let me know if there is any other information I can provide, or if I made anything unclear. I wanted to give full background, links to working code, and examples of what I am getting/doing, and what what I -want- get/do.
推荐答案
事情开始变得非常混乱，因为数据库没有标准化.此查询将为您提供所需的结果.不幸的是，它对于 SQLFiddle 来说太大了......但 rextester 似乎可以处理它 - 这里是新的.
Things are starting to get really messy because the database isn't normalised. This query will give you the results you want. Unfortunately it got too big for SQLFiddle... But rextester seems to handle it - here is the new one.
输出如下:
id drink_id drink_name drink_image drink_desc ingredient_1_dispenser ingredient_1_code ingredient_1_dosage ingredient_1_dispenser_order ingredient_2_dispenser ingredient_2_code ingredient_2_dosage ingredient_2_dispenser_order ingredient_3_dispenser ingredient_3_code ingredient_3_dosage ingredient_3_dispenser_order
1 vdk_org Screw Driver screw_driver.jpg Screw Driver description... bottle vdk 1 1 valve oj 2000 1
2 vdk_cran Cape Cod cape_cod.jpg Cape Cod description... bottle vdk 1 1 valve cbj 2000 2
3 dry_mrtn Dry Martini dry_martini.jpg Dry Martini description... bottle vdk 2.5 1 bottle vrmth .5 7 valve orgbit 200 3
这是更新后的查询:
SELECT id, drink_id, drink_name, drink_image, drink_desc,
ingredient_1_dispenser, ingredient_1_code, ingredient_1_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_1_dispenser AND i.ingredient_code = d.ingredient_1_code), '') AS ingredient_1_dispenser_order,
ingredient_2_dispenser, ingredient_2_code, ingredient_2_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_2_dispenser AND i.ingredient_code = d.ingredient_2_code), '') AS ingredient_2_dispenser_order,
ingredient_3_dispenser, ingredient_3_code, ingredient_3_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_3_dispenser AND i.ingredient_code = d.ingredient_3_code), '') AS ingredient_3_dispenser_order,
ingredient_4_dispenser, ingredient_4_code, ingredient_4_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_4_dispenser AND i.ingredient_code = d.ingredient_4_code), '') AS ingredient_4_dispenser_order,
ingredient_5_dispenser, ingredient_5_code, ingredient_5_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_5_dispenser AND i.ingredient_code = d.ingredient_5_code), '') AS ingredient_5_dispenser_order,
ingredient_6_dispenser, ingredient_6_code, ingredient_6_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_6_dispenser AND i.ingredient_code = d.ingredient_6_code), '') AS ingredient_6_dispenser_order,
ingredient_7_dispenser, ingredient_7_code, ingredient_7_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_7_dispenser AND i.ingredient_code = d.ingredient_7_code), '') AS ingredient_7_dispenser_order,
ingredient_8_dispenser, ingredient_8_code, ingredient_8_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_8_dispenser AND i.ingredient_code = d.ingredient_8_code), '') AS ingredient_8_dispenser_order,
ingredient_9_dispenser, ingredient_9_code, ingredient_9_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_9_dispenser AND i.ingredient_code = d.ingredient_9_code), '') AS ingredient_9_dispenser_order,
ingredient_10_dispenser, ingredient_10_code, ingredient_10_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_10_dispenser AND i.ingredient_code = d.ingredient_10_code), '') AS ingredient_10_dispenser_order,
ingredient_11_dispenser, ingredient_11_code, ingredient_11_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_11_dispenser AND i.ingredient_code = d.ingredient_11_code), '') AS ingredient_11_dispenser_order,
ingredient_12_dispenser, ingredient_12_code, ingredient_12_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_12_dispenser AND i.ingredient_code = d.ingredient_12_code), '') AS ingredient_12_dispenser_order,
ingredient_13_dispenser, ingredient_13_code, ingredient_13_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_13_dispenser AND i.ingredient_code = d.ingredient_13_code), '') AS ingredient_13_dispenser_order,
ingredient_14_dispenser, ingredient_14_code, ingredient_14_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_14_dispenser AND i.ingredient_code = d.ingredient_14_code), '') AS ingredient_14_dispenser_order,
ingredient_15_dispenser, ingredient_15_code, ingredient_15_dosage,
COALESCE((SELECT dispenser_order FROM barbot_ingredients i WHERE i.active = 1 AND i.dispenser_type = d.ingredient_15_dispenser AND i.ingredient_code = d.ingredient_15_code), '') AS ingredient_15_dispenser_order
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 + 调整当前查询以从另一个表中获取额外数据(每列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
