Select from several tables on criteria

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

SQL Server 2000 Forums

SQL Server Development (2000)

Select from several tables on criteria

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-24 : 09:45:59

Mikael writes "Hi I'm wondering if someone can help me with the following sql-problem...

I have three tables, with their respective attributes:

RawMaterial
-----------
RMname(pk)
quantity
unit

Recipe
-------
cookieName(pk, fk)
RMname(pk, fk)
quantity
unit

Cookie
-------
cookieName(pk)

Rawmaterial holds information about raw materials for cookies, that is the raw materials name, quantity in stock and the unit, e.g. grams, liters, pieces etc.

Cookie simply holds names of cookies, e.g. butter cake, chocolate dream etc.

Recipe is a relational table and describes the relation between RawMaterial and Cookie.
Each tuple (row) is identified by the cookieName together with RMname, that is to say both cookieName and RMname form the primary key.

Question:
I need to show ALL cookies and the amount of sugar for each cookie (null for the ones that doesn't have sugar in them)

This query work fine, only problem is it only shows the cookies which has sugar in them:

SELECT Cookie.cookieName, RawMaterial.RMname, Recipe.quantity
FROM RawMaterial, Recipe
LEFT OUTER JOIN Cookie
ON Cookie.cookieName = Recipe.cookieName
WHERE Recipe.RMname = RawMaterial.RMname
AND RawMaterial.RMname = 'Sugar'

The above select-statement would show:

Children Egg Sugar 600.00
Lemon Sugar 225.00
Nutty Cake Sugar 375.00
Nutty Grutty Sugar 190.00

and I can't use ..."RawMaterial.RMname != 'Sugar'" because that would list all rows in Recipe that that doesn't include sugar (see below).

Berserk Cookie Apple flavouring 160.00
Berserk Cookie Butter 500.00
Children Egg Butter 200.00
Lemon Butter 125.00
Nutty Grutty Butter 450.00
Berserk Cookie Chocolate 80.00
Children Egg Chocolate 100.00
Nutty Cake Chocolate 50.00
Children Egg Crap toy 215.00
Nutty Cake Egg 250.00
Berserk Cookie Flour 300.00
Children Egg Flour 215.00
Lemon Flour 100.00
Nutty Grutty Flour 450.00
Children Egg Fried crushed nuts 620.00
Nutty Cake Fried crushed nuts 625.00
Nutty Grutty Fried crushed nuts 225.00
Berserk Cookie Hazelnuts 75.00
Children Egg Hazelnuts 100.00
Lemon Lemon 50.00
Lemon Meal 450.00
Children Egg Milk 1.50
Nutty Cake Nuts 750.00

Any suggestions?

Any help would be greatly appreciated.

Regards
/Mikael W"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources