CODEWITHSUNDEEP
mysqlsqldatabasecomposite
Robert Stivanson
Robert Stivanson

Reputation: 23

SQL multi-table composite key unique query

For starters, some background information. I am building a simple recipe book database and I'm trying to build a query that will show me what I can make based on the items in my pantry. The table schemas are as follows:

RECIPE(*RecipeID*, RecipeName)
RECIPE_INGREDIENT(***RecipeID***, **IngredientID**)
INGREDIENT(*IngredientID*, IngredientName)
PANTRY_ITEM(*ItemID*, ItemName)

The fields in the RECIPE_INGREDIENT table make up a composite key and are both foreign keys into the RECIPE(RecipeID) and INGREDIENT(IngredientID) table. I'm using the following test data:

RECIPE table
RecipeID,RecipeName
1,'food 1'
2,'food 2'

INGREDIENT Table
IngredientID, IngredientName
1,'ing 1'
2,'ing 2'
3,'ing 3'

RECIPE_INGREDIENT table
RecipeID,IngredientID
1,1
1,2
2,2
2,3

PANTRY_ITEM table
ItemID,ItemName
1,'ing 2'
2,'ing 3'

So essentially I'm trying to query a list of RecipeNames based on the items I have in my pantry. By this, I mean that I must have all the ingredients for the recipe in my pantry for it to be added to the list. Therefore the ideal query based on this test data would result in only 'food 2'. The problem I'm running into is enforcing the 'all ingredients' for a recipe part.

I've tried a few different queries and they all result in every recipe being returned.

SELECT RecipeName FROM RECIPE WHERE RecipeID IN (SELECT RecipeID FROM RECIPE_INGREDIENT WHERE IngredientID IN (SELECT IngredientID FROM INGREDIENT WHERE IngredientName IN (SELECT ItemName FROM PANTRY_ITEM)))

Does anyone have any ideas as to how I can accomplish this? Is there a possible query for this, or would I have to restructure my database?

Upvotes: 0

Views: 308

Answers (4)

TommCatt
TommCatt

Reputation: 5636

Actually, I don't see why you need more than the two tables. Your Recipes table needs two more fields. A unit of measurement (tsp, cup, oz, etc.) and how many of that measurement is needed for the recipe. The Ingredients table needs the same two fields only this is the amount available in the pantry. You need the other unit of measurement because a recipe may call for a cup of sugar but you buy sugar by the pound.

You'll also need a udf to convert from one unit to another so if the recipe calls for 2 tsp vanilla and the pantry shows 1 oz left, is that enough?

Then just perform an outer join between recipes and ingredients and ignore those with any null ingredients (no or not enough of an ingredient in the pantry).

Upvotes: 0

Fathah Rehman P
Fathah Rehman P

Reputation: 8751

Here is your answer

select r1.recipeName from recipe r1 where r1.recipeName not in 
(
select r.recipeName from recipe r
inner join 
recipe_ingredient ri
on r.recipeid=ri.RecipeID
inner JOIN
ingredient i
on ri.IngredientID=i.ingredientid and 
i.IngredientName not in (select itemname from pantry_item) )

Upvotes: 0

Frank Bardon Jr.
Frank Bardon Jr.

Reputation: 145

This WILL NOT perform well, but it's possible without changing your schema. I'd recommend though, perhaps you should consider having your PANTRY table id's match the ingredient id's...

Essentially, we have to find any time a NULL value is presented when comparing a recipe ingredient to it's pantry item equivalent. Any instance of NULL in the sub query means there is a missing item... So we just generate an exclusion list and compare the results to that.

SELECT RecipeID AS _id, RecipeName FROM RECIPE WHERE RecipeID NOT IN (
  SELECT d.RecipeId FROM (
    SELECT p.ItemID, r.RecipeID FROM PANTRY_ITEM p
      JOIN INGREDIENT i ON (p.ItemName = i.IngredientName)
      RIGHT JOIN RECIPE_INGREDIENT ri ON (ri.IngredientId = i.IngredientId)
      RIGHT JOIN RECIPE r ON (r.RecipeId = ri.RecipeId)
  ) d WHERE d.ItemID IS NULL
);

Upvotes: 0

peterm
peterm

Reputation: 92805

First off if you can put only ingredients into the pantry your pantry schema should rather look like this

CREATE TABLE pantry
(
  IngredientID int,
  FOREIGN KEY (IngredientID) REFERENCES ingredient (IngredientID)
);

Now, you can leverage HAVING clause to get the desired result

SELECT recipename
  FROM
(
  SELECT recipeid
    FROM recipe_ingredient ri LEFT JOIN pantry p
      ON ri.ingredientid = p.ingredientid
   GROUP BY recipeid
   HAVING COUNT(*) = COUNT(p.ingredientid)
) q JOIN recipe r
    ON q.recipeid = r.recipeid

Output:

| RecipeName |
|------------|
|     food 2 |

Here is a SQLFiddle demo

Upvotes: 1

Related Questions