CODEWITHSUNDEEP
sqldatabasesqlite
SteveIrwin
SteveIrwin

Reputation: 115

Database Selecting Multiple Rows Based on Multiple Values

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.

Upvotes: 0

Views: 50

Answers (1)

jpw
jpw

Reputation: 44871

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.

Upvotes: 1

Related Questions