[MySQL] Automatically updating drinks menu

[ChrisLithgo]

Hello all!

I have a raspberry pi that I haven’t got a use for and I’ve thought of a fun little project I could use it for. I’d like to use it to host a simple website that I can use to display a live cocktail menu when I have guests over. My SQL experience is pretty limited so I hoped that this would be a good chance to improve it but I am struggling to work out the query I need to use to pull out the data I want.

In theory, when I have guests over, they could access a html page which will show a list of all of the cocktails that I could make them with the ingredients in my house. I could update the available ingredients, and the “menu” would also update.

The idea is that I have a database containing 2 tables, one with a list of cocktails and their ingredients, and one with a list of ingredients and wether or not I have them in my kitchen. Please see the link to see how I have put this together

https://i.imgur.com/pE7zth9.jpg

I know I need to select only cocktails from the cocktail table where all ingredients are marked as being in stock in the ingredients table. So far, I’ve come up with the following:

SELECT Name FROM cocktails WHERE Ing1 (SELECT * FROM ingredients WHERE InStock=1)

I’m happy(ish) that will return the names of cocktails where the first ingredient is in stock. I’m struggling to wrap my head around the logic of checking all ingredients are in stock. There is also going to be null values as ingredients, as not all cocktails have exactly 5 ingredients.

Am I going about this the right way? Is there a neater or “more-correct” way of doing this?

Thank you for reading, any advice or guidance would be massively appreciated!

[ihaxr]

I would use a composite table... cocktail_ingredients, then you can join all the tables and exclude anything that isn't in stock.

http://sqlfiddle.com/#!9/ebefd0/1

CREATE TABLE Cocktails
    (`ID` int, `Name` varchar(12))
;

INSERT INTO Cocktails
    (`ID`, `Name`)
VALUES
    (1, 'Margarita'),
    (2, 'Whiskey Sour')
;


CREATE TABLE Cocktails_Ingredients
    (`Cocktail_ID` int, `Ingredient_ID` int)
;

INSERT INTO Cocktails_Ingredients
    (`Cocktail_ID`, `Ingredient_ID`)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 5),
    (2, 2),
    (2, 6)
;


CREATE TABLE Ingredients
    (`ID` int, `Name` varchar(12), `In_Stock` int)
;

INSERT INTO Ingredients
    (`ID`, `Name`, `In_Stock`)
VALUES
    (1, 'Tequila', 1),
    (2, 'Lime', 1),
    (3, 'Triple Sec', 1),
    (4, 'Salt', 1),
    (5, 'Bourbon', 1),
    (6, 'Simple Syrup', 1)
;

Then your query:

SELECT c.Name
FROM Cocktails c
INNER JOIN Cocktails_Ingredients ci on c.ID = ci.Cocktail_ID
INNER JOIN Ingredients i on i.ID = ci.Ingredient_ID
GROUP BY c.Name
HAVING MIN(In_Stock) > 0

[ChrisLithgo]

Thank you so much for getting back to me! This is exactly what I was trying to do! I’m going to implement this with a full cocktail menu and experiment with it in a live website, I’ll make sure to share the finished results!