Clash Royale CLAN TAG#URR8PPP

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.

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. :)

I have an example Rextester set-up for you to look at as well:

http://rextester.com/FWVO47690

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:

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?

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..

If a drink needs:

orange juice and vodka..

It will not be active or display in the drink menu if in the ingredients table orange juice or vodka is not active...etc..

** 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)

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:

** 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..

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.

@Nick (like this?)

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));


re-cap:

I only want to return drinks, from the drinks table that:

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)

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

1 Answer
1

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


Edit

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))


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.