CODEWITHSUNDEEP
sqlms-accessms-access-2010
sigil
sigil

Reputation: 9546

Why am I getting a syntax error on these joins?

I'm getting the

Syntax error in JOIN operation

error on this query. This suggests that there's a misplaced parenthesis somewhere in the joins, but I can't figure out what's wrong.

select *
from
((ss
left join
sc
on
ss.guid=sc.guid)
left join
mrc
on
format(c.xDate, "yyyymmddHHMMSS")=mrc.xDate)
left join
c
on
sc.cID=c.id

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You are getting an error because c is referenced before it is defined:

select *
from ((ss left join
       sc
       on ss.guid = sc.guid
      ) left join
      mrc
      on format(c.xDate, "yyyymmddHHMMSS") = mrc.xDate
----------------^
     ) left join
     c
     on sc.cID = c.id

You can fix this by swapping the joins:

select *
from ((ss left join
       sc
       on ss.guid = sc.guid
      ) left join
      c
      on sc.cID = c.id
     ) left join
     mrc
     on format(c.xDate, "yyyymmddHHMMSS") = mrc.xDate

Upvotes: 3

Related Questions