Author Topic: MySQL Question  (Read 970 times)

0 Members and 1 Guest are viewing this topic.

Offline Sleepy

  • Fuck Yes Sunshine In a Bag
  • The Beast
  • *****
  • Posts: 4598
  • Gender: Female
  • Danger zone
MySQL Question
« on: March 23, 2014, 08:16:21 pm »
My group is building a website in our databases class, and I can't figure out how to write a query I need. There are a few tables in the database. The ones this query will be using are called Recipes, Ingredients, and a junction table called RecipeIngredients. Here is what each table contains:

Recipes:
recipe_id
recipe_name
recipe_creator
recipe_rating

Ingredients:
ingredient_id
ingredient_name

RecipeIngredients:
recipe_id (foreign key)
ingredient_id (foreign key)
PK_recipe_ingredients (primary key)

The website we've created allows the user to search for recipes by ingredient. This query needs to take that searched ingredient (defined as searchTerm in our code) and select all recipes that contain that ingredient. Because we have a junction table, that requires the use of JOIN, which I'm still trying to understand. This is what I have currently, but it's not complete and I don't think it's entirely correct, anyway.

Select * from Recipes
JOIN Ingredients ON Ingredients.ingredient_id = RecipeIngredients.ingredient_id
JOIN RecipeIngredients ON Recipes.recipe_id = RecipeIngredients.recipe_id

Obviously it needs more, like the searchTerm, but I can't figure out where to stick it. Any ideas? I know the question is oddly specific, but we have a lot of good comp sci folks, so I figured I'd ask here, as well.
Guys, this is getting creepy. Can we talk about cannibalism instead?

If a clown eats salmon on Tuesday, how much does a triangle weigh on Jupiter? Ask Mr. Wiggins for 10% off of your next dry cleaning bill. -Hades

Offline Dan

  • Apprentice
  • **
  • Posts: 99
  • Gender: Male
Re: MySQL Question
« Reply #1 on: March 24, 2014, 04:30:20 pm »
I've never been all that hot on SQL, but your searchTerm goes in a WHERE clause that you can just add to the end of what you already have. (I don't know if that part is correct, but it looks ok to me.)

I know it's a slightly different dialect, but I find the SQLite documentation very good: http://www.sqlite.org/lang.html

From which it looks like you may need a comma before the first JOIN. And possibly before the second too.

Then you'd get something like:
Code: [Select]
Select * from Recipes,
JOIN Ingredients ON Ingredients.ingredient_id = RecipeIngredients.ingredient_id,
JOIN RecipeIngredients ON Recipes.recipe_id = RecipeIngredients.recipe_id
WHERE ingredients.ingredient_name = searchTerm

Good luck!

Offline Sleepy

  • Fuck Yes Sunshine In a Bag
  • The Beast
  • *****
  • Posts: 4598
  • Gender: Female
  • Danger zone
Re: MySQL Question
« Reply #2 on: March 24, 2014, 06:10:57 pm »
I was actually going to try that, but for some reason the WHERE seems out of place to me, like it's irrelevant with the previous lines. But thanks, I'll see if that works.
Guys, this is getting creepy. Can we talk about cannibalism instead?

If a clown eats salmon on Tuesday, how much does a triangle weigh on Jupiter? Ask Mr. Wiggins for 10% off of your next dry cleaning bill. -Hades