MS Access and nested INNER JOINs

Example SQL statement 1 works OK …

SELECT u.Field1,s.Field2,u.Field3
FROM utable u
INNER JOIN stable s on s.fieldid = u.afield

Example SQL statement 2 works OK …

SELECT u.Field1,t.Field2,u.Field3
FROM utable u
INNER JOIN ttable t on t.fieldid = u.bfield

Example SQL statement 3 does not work …

SELECT u.Field1,s.Field2,t.field2,u.Field3
FROM utable u
INNER JOIN stable s on s.fieldid = u.afield
INNER JOIN ttable t ON t.fieldid = u.bfield

i.e. Each individual JOIN works fine, but as soon as I try to cascade the JOINs then Access throws an error. (I’m not at my dev PC so I can’t remember the precise wording of the error). The idea is to get the value linked to an ID in a linked table. (e.g. the name of the US state from its ID reference).

I’m sure I’ve done similar things in MySQL before, in fact much more complex JOINs, without issues. Does Access have a known issue with what I’m trying to do? (Or do I need a subtle different syntax for Access).

Note - I have not shown a WHERE clause but I know the problem does not lie there. e.g. it would be similar to WHERE u.Field1 LIKE ‘%somevalue%’

TIA for any help offered.

I’m not familiar with Access but if I remember rudy’s postings correctly you need to wrap the joins inside ( and ) :slight_smile:

You mean like this?

SELECT u.Field1,s.Field2,t.field2,u.Field3
FROM utable u
INNER JOIN (stable s on s.fieldid = u.afield)
INNER JOIN (ttable t ON t.fieldid = u.bfield)

Didn’t work. Got a Syntax error in FROM clause message.

Here is my actual SQL query …

SELECT u.Company,u.Address,u.City,s.StateName,u.ZIP,n.Country,
u.Phone,u.Fax,u.Email,u.Web,u.ContactType
FROM USALocators u
INNER JOIN States s ON s.StateID = u.State
INNER JOIN NACountries n ON n.CountryID = u.Country
WHERE (u.ContactType = 1 OR u.ContactType = 2)

and here is the error message …

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 's.StateID = u.State INNER JOIN NACountries n ON n.CountryID = u.Country'.

Maybe something like


select u.Company
     , u.Address
     , u.City
     , s.StateName
     , u.ZIP
     , n.Country
     , u.Phone
     , u.Fax
     , u.Email
     , u.Web
     , u.ContactType
  from (
    USALocators as u
      inner
    	  join States as s
    	    on s.StateID = u.State
    )
    inner
      join NACountries as n
        on n.CountryID = u.Country
  where u.ContactType = 1
     or u.ContactType = 2

:cool: Works great. Thank you.