Database Selecting Multiple Rows Based on Multiple Values

Question

I am designing a database to store cocktail name and their ingredients. The database has following structure.

Drinks      
ID  NAME    IMGURL
1   A      http://
2   B      http://
3   C      http://

Ingredients 
ID  NAME
1   Water
2   SugarSyrup
3   Rum
4   Vodka
5   Gin
6   Tonic
7   Orange Juice
8   Whiskey

DRINKS_INGREDIENTS          
ID  DRINKSID    INGREDIENTSID   Quantity
1   1   1   30
2   1   4   90
3   1   6   60
4   2   8   30
5   2   1   30
6   2   2   30
7   2   3   10
8   2   4   30
9   3   5   60
10  3   6   60

Stocks  
StockID IngredientID
1   5
2   6

I want to select only the DRINKSIDs from DRINKS_INGREDIENTS table where the ingredients equals to the ones we have in stock.ex- if we only have Gin and Tonic in Stock, then it should return only DrinkID=3.If we have all the ingredients in stock, then it will return the all the DrinkIDs in the list.

We can also assume that the stocks are infinite so quantity check is not required.

I am using sqlite database and I am not sure how to write a query to make this work.

How will you decide whether you have all ingredients doe a drink are in stock? — Saharsh Shah
– Saharsh Shah, Commented Jan 5, 2015 at 11:25
Someone updates those values everyday manually and so we know that beforehand.The values are stored in an array of Strings. — SteveIrwin
– SteveIrwin, Commented Jan 5, 2015 at 11:27
But I want to ask that for drink A which ingredients you should check sameway fror drink B which ingredients to be checked — Saharsh Shah
– Saharsh Shah, Commented Jan 5, 2015 at 11:29
Probably my question is not clear, so I will make another attempt at it.So I have a database of drinks but I only have certain ingredients in stock.I only want to present the user with drinks which can be made with the ones we have in stock.So essentially I want a query which will only return the DRINKID for drinks whose ingredients are currently in stock.Hope it helps. — SteveIrwin
– SteveIrwin, Commented Jan 5, 2015 at 11:31
I understand but my question is still pending that how can i decide which drinks want which ingredients to be used? — Saharsh Shah
– Saharsh Shah, Commented Jan 5, 2015 at 11:32

jpw · Accepted Answer · 2015-01-05 12:36:30Z

1

To find what drinks you can make given ingredients in stock you can do an inner join between two queries. The first will give you the count of ingredients needed to make each drink, and the other will give you the count of ingredients in stock for each drink. By joining the queries on drinks.ID and count you'll get the drinks that have all ingredients needed in stock.

This query should do what you want:

select drinks.name from (
    select d.NAME, di.DRINKSID, count(*) as cnt 
    from DRINKS_INGREDIENTS di
    inner join Drinks d on d.ID = di.DRINKSID
    group by d.name, di.DRINKSID
) drinks
inner join (
    select di.DRINKSID, count(*) as cnt 
    from DRINKS_INGREDIENTS di
    inner join stock s on di.INGREDIENTSID = s.IngredientID
    group by di.DRINKSID
) stock 
on drinks.DRINKSID = stock.DRINKSID
    and drinks.cnt = stock.cnt;

Sample SQL Fiddle

It would be easy to modify it to take quantity in stock in account too, just add quantity to the stocks table, and modify the join in the second query to include s.quantity >= di.quantity. See this sample.

edited Jan 5, 2015 at 12:36

answered Jan 5, 2015 at 12:28

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SteveIrwin Over a year ago

Thanks a lot.The solution looks great .The quantity check is an added advantage.

Collectives™ on Stack Overflow

Database Selecting Multiple Rows Based on Multiple Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related