SQL select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL select statement

Author

Topic

vickp07
Starting Member

2 Posts

Posted - 2011-09-26 : 17:21:49

Hey guys,

so i am having trouble trying to figure out what my select statement should look like.

Here is a look at my tables and rows:
CREATE TABLE recipes
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(128) NOT NULL UNIQUE,
description VARCHAR(1024) NOT NULL,
preptime INT NOT NULL,
totaltime INT NOT NULL,
rating DOUBLE NOT NULL
);

CREATE TABLE steps
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
recipe_id INT NOT NULL,
stepno INT NOT NULL,
text VARCHAR(1024) NOT NULL,
FOREIGN KEY (recipe_id) REFERENCES recipes(id)
);

CREATE TABLE ingredients
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(45) NOT NULL,
unit_id INT NOT NULL,
FOREIGN KEY (unit_id) REFERENCES units_of_measure(id)
);

CREATE TABLE units_of_measure
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL
);

CREATE TABLE recipe_ingredient
(
recipe_id INT NOT NULL,
ingredient_id INT NOT NULL,
amount DOUBLE NOT NULL,
FOREIGN KEY (recipe_id) REFERENCES recipes(id),
FOREIGN KEY (ingredient_id) REFERENCES ingredients(id),
UNIQUE(recipe_id,ingredient_id)
);

Now what I want to do is (i am trying to just view the data for recipe #1) :
SELECT units_of_measure.name, ingredients.name FROM units_of_measure, ingredients, recipes
WHERE ***** THIS IS WHERE I AM HAVING TROUBLE I DUNNO HOW TO WRITE THIS WHERE CLAUSE**********
ingredients.unit_id = units_of_measure.id
AND
recipe.id = 1;

vickp07
Starting Member

2 Posts

Posted - 2011-09-26 : 17:22:11

okay so let me explain a lilttle more.....
in the recipe_ingredient table it holds the (amount) field which for example is:
1
or
2
or
.5 for 1/2

then in the units_of_measure table
it contains (name) which is something like:
teaspoon or tablespoon

then in the ingredients table it contains the (name) field which contains something like:
salt
or
eggs

The ingredients.unit_id contains the number that links it to the units_of_measure.id

In the recipe_ingredient table
the recipe_id and ingredient_id contains the number that links it to recipe.id and ingredients.id

Kristen
Test

22859 Posts

Posted - 2011-09-26 : 17:57:53

"AUTO_INCREMENT"

Is that MySQL? This is a Microsoft SQL Server forum, so you may not find people here who are able to answer your question I'm afraid.

I would have written something like:


SELECT M.name, I.name
FROM recipes AS R
    JOIN recipe_ingredient AS RI
        ON RI.recipe_id = R.id
    JOIN ingredients AS I
        ON I.id = RI.ingredient_id 
    JOIN units_of_measure AS M
        ON M.id = I.unit_id
WHERE R.id = 1

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-09-27 : 07:59:16

Hii ,

I understand that you want to query a certain recipes (#1) and its ingredients

this can be done in sqlserver as

select recipes.name , recipes.description,recipes.preptime,recipes.totaltime,recipes.rating,
ingredients.name ,units_of_measure.name from recipes
inner join recipe_ingredient
on recipes.id = recipe_ingredient.recipe_id
inner join ingredients
on recipe_ingredient.ingredient_id = ingredients.id
inner join units_of_measure
on ingredients.unit_id= units_of_measure.id

where recipes.id = 1

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 08:28:25

Is that different to what I suggested?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-09-27 : 08:42:40

quote:
Originally posted by Kristen

Is that different to what I suggested?

Why yes. It allows me to start a spam account with a couple good answers.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2011-09-27 : 08:51:02

Chuck me your crystal ball as I need to look up next week's lottery numbers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources