Inner Join help

[Bell1991]

I am not familar with Access SQL - but i have a query and am attempting to covert it:

SELECT
txtCountyName,
txtCityName
FROM tblTest
INNER JOIN tblCounty on tblTest.numCountyID = tblCounty.numCountyID
INNER JOIN tblCity on tblTest.numCityID = tblCity.numCityID

Any ideas on what i am doing wrong?

Thanks

 

[r937]

it's not you, it's access

access requires more than two tables being joined to be parenthesized

SELECT txtCountyName
     , txtCityName 
  FROM [b][COLOR=red]([/color][/b]
       tblTest
INNER 
  JOIN tblCounty 
    on tblTest.numCountyID 
     = tblCounty.numCountyID 
       [b][COLOR=red])[/color][/b]
INNER 
  JOIN tblCity 
    on tblTest.numCityID 
     = tblCity.numCityID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Bell1991]

Thanks

 

[Bell1991]

What if you have more than 2 tables?

 

[r937]

you mean more than 3, right? 'cause the example i gave had 3 tables already

then you need more than one level of parentheses

  FROM (((
       table1 as A
INNER 
  JOIN table2 as B
    on A.foo = B.foo
       )
INNER 
  JOIN table3 as C
    on B.bar = C.bar
       )
INNER 
  JOIN table4 as D
    on C.qux = D.qux
       )
INNER 
  JOIN table5 as E
    on D.fap = E.fap

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Bell1991]

Thanks again - i had the parenthesis in the wrong place..