sql question on using "as"

[EzIA]

Hi all
This should be simple enough but I cant seem to get it to work

Lets say I have a table "recepies"

| Formula | Ingredient | //these are the fields' names
----------------------------------
| cake | water |
| cake | flour |
| dough | water |
| dough | flour |
| tea | water |
| tea | extract |


I need a query that will give me all the formulas with "water" and "flour" listed in the table (ie - cake, dough).

I know I have to do something with the "as" keyword but Im not very fluent in SQL. Can someone lend a helping hand?

Thanks
IA

 

[Mr. B]

Give this a try:

SELECT * FROM receipies
WHERE receipies.Ingredient="water" OR receipies.Ingredient="flour";

 

[EzIA]

if only it were that simple....

Thanks for the answer but this way I will also get "tea" in the results since your query uses "OR".
I know I should be using "AS" and "inner join" to intersect 2 queries but i cant seem to get the syntax to work...

 

[tehNellie]

It'd be a lot easier if your table was properly normalised

SELECT f.Formula
FROM table1 as f
INNER JOIN 
(
 Select Formula
 FROM table1
 WHERE Ingredient = "water"
) as w on
 f.formula = w.formula
Where f.ingredient = "flour"

 

[EzIA]

it seems i needed something like this:

SELECT Q0.formula FROM recepies AS Q0
INNER JOIN recepies AS Q1 ON Q0.formula=Q1.formula WHERE Q0.Ingredient='water' AND Q1.ingredient='dough'

 

[EzIA]

thanks tehNellie!
I just got the answer after a lot of trial and error.
It is very similar to yours so thanks again
My problem was the syntax and the fact that using INNER JOIN I need to work with a hierarchy of brackets....
I'm not realy sure what U mean by "normalised" but normal or not' I eventually got there
IA