Barbot #6, MySQL - rextester

Language: Layout:

drop table if exists barbot_ingredients;
drop table if exists barbot_drinks;

create table barbot_ingredients(
    date_added datetime,
    active bool,
    dispenser_type varchar(100),
    dispenser_order int(2),
	brand_name varchar(255),
	brand_code varchar(255),	
	ingredient_type varchar(255),
	ingredient_code varchar(10)                
);

create table barbot_drinks(
    id int(11),
	drink_id varchar(11),
	drink_name varchar(255),
	drink_image varchar(255),
	drink_desc TEXT,
	ingredient_1_dispenser varchar(100),
	ingredient_1_code varchar(100),
	ingredient_1_dosage varchar(100),
	ingredient_2_dispenser varchar(100),
	ingredient_2_code varchar(100),
	ingredient_2_dosage varchar(100),
	ingredient_3_dispenser varchar(100),
	ingredient_3_code varchar(100),
	ingredient_3_dosage varchar(100),
	ingredient_4_dispenser varchar(100),
	ingredient_4_code varchar(100),
	ingredient_4_dosage varchar(100),
	ingredient_5_dispenser varchar(100),
	ingredient_5_code varchar(100),
	ingredient_5_dosage varchar(100),
	ingredient_6_dispenser varchar(100),
	ingredient_6_code varchar(100),
	ingredient_6_dosage varchar(100),
	ingredient_7_dispenser varchar(100),
	ingredient_7_code varchar(100),
	ingredient_7_dosage varchar(100),
	ingredient_8_dispenser varchar(100),
	ingredient_8_code varchar(100),
	ingredient_8_dosage varchar(100),
	ingredient_9_dispenser varchar(100),
	ingredient_9_code varchar(100),
	ingredient_9_dosage varchar(100),
	ingredient_10_dispenser varchar(100),
	ingredient_10_code varchar(100),
	ingredient_10_dosage varchar(100),
	ingredient_11_dispenser varchar(100),
	ingredient_11_code varchar(100),
	ingredient_11_dosage varchar(100),
	ingredient_12_dispenser varchar(100),
	ingredient_12_code varchar(100),
	ingredient_12_dosage varchar(100),
	ingredient_13_dispenser varchar(100),
	ingredient_13_code varchar(100),
	ingredient_13_dosage varchar(100),
	ingredient_14_dispenser varchar(100),
	ingredient_14_code varchar(100),
	ingredient_14_dosage varchar(100),
	ingredient_15_dispenser varchar(100),
	ingredient_15_code varchar(100),
	ingredient_15_dosage varchar(100)
);

insert into barbot_ingredients(active,dispenser_type,dispenser_order,brand_name,brand_code,ingredient_type,ingredient_code) values
    (0, 'bottle', 1, 'Kettle One Vodka', 'ko_vdk', 'vodka', 'vdk'),
	(1, 'bottle', 2, 'Jack Daniels Whiskey', 'jd_wsk', 'whiskey', 'wsk'),
	(0, 'bottle', 3, 'Jose Cuervo (Silver)', 'jcs_teq', 'tequila', 'teq2'),
	(1, 'bottle', 4, 'Jose Cuervo (Gold)', 'jcg_teq', 'tequila', 'teq'),
	(1, 'bottle', 5, 'Captain Morgans Spiced Rum', 'cm_rum', 'rum', 'rum'),
	(1, 'bottle', 6, 'Tanqueray Gin', 'tq_gin', 'gin', 'gin'),
	(0, 'bottle', 6, 'Seagrams Gin', 'sg_gin', 'gin', 'gin'),
	(1, 'bottle', 1, 'Grey Goose', 'gg_vdk', 'vodka', 'vdk'),
	(1, 'bottle', 7, 'Vermouth', 'gen_vrmth', 'vermouth', 'vrmth'),
	(1, 'bottle', 8, 'Sweet Vermouth', 'swt_vrmth', 'vermouth', 'swt_vrmth'),
	(1, 'valve', 1, 'Orange Juice', 'gen_oj', 'juice', 'oj'),	
	(1, 'valve', 2, 'Cranberry Juice', 'gen_cran', 'juice', 'cbj'),
	(1, 'valve', 3, 'Orange Bitters', 'org_bitters', 'bitters', 'orgbit'),
	(1, 'valve', 4, 'Angostura bitters', 'ang_bitters', 'bitters', 'angbit');

insert into barbot_drinks(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, ingredient_5_dispenser, ingredient_5_code, ingredient_5_dosage, ingredient_6_dispenser, ingredient_6_code, ingredient_6_dosage, ingredient_7_dispenser, ingredient_7_code, ingredient_7_dosage, ingredient_8_dispenser, ingredient_8_code, ingredient_8_dosage, ingredient_9_dispenser, ingredient_9_code, ingredient_9_dosage, ingredient_10_dispenser, ingredient_10_code, ingredient_10_dosage, ingredient_11_dispenser, ingredient_11_code, ingredient_11_dosage, ingredient_12_dispenser, ingredient_12_code, ingredient_12_dosage, ingredient_13_dispenser, ingredient_13_code, ingredient_13_dosage, ingredient_14_dispenser, ingredient_14_code, ingredient_14_dosage, ingredient_15_dispenser, ingredient_15_code, ingredient_15_dosage) values
    ('vdk_org', 'Screw Driver', 'screw_driver.jpg', 'Screw Driver description...', 'bottle','vdk', '1', 'valve', 'oj', '2000', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' ),
    ('vdk_cran', 'Cape Cod', 'cape_cod.jpg', 'Cape Cod description...', 'bottle','vdk', '1', 'valve', 'cbj', '2000', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' ),
	('dry_mrtn', 'Dry Martini', 'dry_martini.jpg', 'Dry Martini description...', 'bottle','vdk', '2.5', 'bottle', 'vrmth', '.5', 'valve', 'orgbit', '200', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' ),
	('manhattan', 'The Manhattan', 'manhattan.jpg', 'Manhattan description...', 'valve','angbit', '400', 'valve', 'orgbit', '200', 'bottle','brbn', '1', 'bottle', 'swt_vrmth', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
    ('fake_drnk', 'Fake Drink', 'fake_drink.jpg', 'Fake Drink description...', 'bottle','fke', '1', 'valve', 'cbj', '2000', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' );

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


absolute service time: 0,47 sec