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.
Here is the output:
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
Here's the updated query:
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))