Multiple Joins, Missing operator

[Tokhra]

Hey all,

Using Access 2000 im trying to do:

SELECT t1.Field1, t2.Field2, t3.Field3 FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Field = t2.Field INNER JOIN Table3 AS t3 ON t1.Field = t3.Field WHERE t1.Field = 1 ORDER BY t1.Field

Except access errors saying its missing an operator, if I reduce the query to one INNER JOIN then its fine.

Have I got the syntax wrong? Any Ideas?

Thanks,
Matt.

 

[amorous]

Try this:

SELECT t1.Field1, t2.Field2, t3.Field3 FROM Table1 AS t1 INNER JOIN (Table2 AS t2 ON t1.Field = t2.Field INNER JOIN (Table3 AS t3 ON t1.Field = t3.Field)) WHERE t1.Field = 1 ORDER BY t1.Field

-VJ

 

[amorous]

Hmm...
Try this too

SELECT t1.Field1, t2.Field2, t3.Field3 FROM Table1 AS t1 INNER JOIN (Table2 AS t2 ON t1.Field = t2.Field) INNER JOIN (Table3 AS t3 ON t1.Field = t3.Field) WHERE t1.Field = 1 ORDER BY t1.Field

Observe the parenthesis...

-VJ

 

[Tokhra]

Hey Amorous,

Thanks for the reply.

I've just tried those and neither work, invalid syntax on join operation

 

[amorous]

Try this:

SELECT t1.Field1, t2.Field2, t3.Field3 FROM Table1 AS t1 (INNER JOIN Table2 AS t2 ON t1.Field = t2.Field INNER JOIN Table3 AS t3 ON t1.Field = t3.Field) WHERE t1.Field = 1 ORDER BY t1.Field

Observe the parenthesis...

Your error...missing an operator is referring to a missing parenthesis...

-VJ

 

[PHV]

You may also try this:
SELECT t1.Field1, t2.Field2, t3.Field3
FROM Table1 AS t1, Table2 AS t2, Table3 AS t3
WHERE t1.Field = 1 AND t2.Field = t1.Field AND t3.Field = t1.Field
ORDER BY t1.Field

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244