>>>I seem to keep getting syntax errors if I try to use WHERE and JOIN ON in the same SQL query. Can you use a where clause in an SQL statement that is written as a JOIN, or do all the "WHERE" conditions for both tables have to be in the ON clause?
>>>
>>>Does anybody have a "personal favorite" suggestion for an advanced SQL tutorial / reference?
>>>
>>>Thanks very much.
>>
>>I routinely use JOIN ON syntax to relate tables with a following WHERE clause to filter the results using fields from one or more of the joined tables.
>>
>>The best thing I've seen is "Taming Visual FoxPro's SQL" by Tamar Granor with Della Martin. It's available as an Ebook from Henzenwerke Publishing,
http://www.hentzenwerke.com/.
>>
>>Regards,
>
>Thanks very much. I wonder if it's the positioning I'm doing wrong. A statement like:
>
select a.field1, a.field2, b.fieldfromb ;
>where a.field1="X" ;
>from table1 a ;
>inner join table2 b on b.somefield=a.somefield
>gives me an error, but if I change the statement to
>
select a.field1, a.field2, b.fieldfromb ;
>from table1 a ;
Right, the JOINs have to happen before the WHERE clause AFAIK.
Regards,
>inner join table2 b on b.somefield=a.somefield and a.field1="X"
then the error goes away. ????
Jim