SQL Left join with select criteria

doze · #1 (**permalink**) 02-16-05, 14:42

Hey, maybe I posted this to the wrong section first, but here:

http://www.dbforums.com/microsoft-access/1117963-sql-left-join-problem.html#post4168806

I got a problem with creating a sql query from multiple tables and use one table as left join. I get "syntax error in join operation".

Pat Phelan · #2 (**permalink**) 02-16-05, 15:20

Look at the suggestion from RedNeckGeek. You can't mix SQL-89 with SQL-92 syntax in Jet (the database engine used by MS-Access).

-PatP

r937 · #3 (**permalink**) 02-17-05, 01:01

pat, if by "mix SQL-89 with SQL-92 syntax" you're referring to the comma-delimited list of tables (implicit inner or cross join) with JOIN syntax, then yes, you can

the syntax error comes from the fact that access requires joins to be parenthesized if there are more than two tables involved

Code:

  from (
       (
       (
       hiihtokerta
inner 
  join hiihtaja 
    on hiihtokerta.hiihtaja_id = hiihtaja.hiihtaja_id
       )
inner 
  join suksi 
    on hiihtokerta.suksi_id = suksi.suksi_id
       )
inner 
  join voide 
    on hiihtokerta.voide_id = voide.voide_id
       )
left outer
  join tavoite 
    on (
       hiihtokerta.paivamaara = tavoite.paivamaara 
   and hiihtokerta.hiihtaja_id = tavoite.hiihtaja_id
       )
 where ...

RedNeckGeek · #4 (**permalink**) 02-17-05, 07:31

Quote:

Originally Posted by r937

the syntax error comes from the fact that access requires joins to be parenthesized if there are more than two tables involved

Rudy,
That isn't necessarily true. By default, when using the query builder, Access will insert all those parentheses. However, the query will run just fine when you delete them. In most of the SQL i generate using VBA, I don't bother with them. The solution I supplied to Doze worked for me the way I built it,
using my own data in a similar manner to what he was doing.

r937 · #5 (**permalink**) 02-17-05, 07:38

hmm, that's a new one on me, thanks

perhaps they took that requirement out in a very recent release of Access

i know it to be a requirement in at least Access 2000

Pat Phelan · #6 (**permalink**) 02-17-05, 08:32

The MS-Access GUI generates more parenteses than a LISP program, but Jet hasn't needed them for at least a couple of years. I don't use Jet a lot, but I've never tried to mix SQL-89 syntax (comma separated tables in the FROM, join criteria in the WHERE) with SQL-92 or later syntax (using the JOIN ... ON syntax in the FROM only).

It might well be possible, but I've never gotten it to work.

-PatP

r937 · #7 (**permalink**) 02-17-05, 11:12

Quote:

Originally Posted by Pat Phelan

The MS-Access GUI generates more parenteses than a LISP program, but Jet hasn't needed them for at least a couple of years.

as i said, maybe a very recent version of access has dropped this requirement but it is still necessary in Access 2000

create these three tables in access:

pat1
id descr
1 foo
2 bar
3 qux

pat2
id descr
1 one
2 two
3 three

pat1pat2
pat1id pat2id
1 2
1 3
2 1
2 3
3 1
3 2
3 3

then try running this query:

Code:

select pat1.descr as pat1descr
     , pat2.descr as pat2descr
  from pat1 
inner 
  join pat1pat2 
    on pat1.id = pat1pat2.pat1id
inner
  join pat2
    on pat1pat2.pat2id = pat2.id

syntax error (missing operator) in query expression

however, if you add the parentheses:

Code:

select pat1.descr as pat1descr
     , pat2.descr as pat2descr
  from (
       pat1 
inner 
  join pat1pat2 
    on pat1.id = pat1pat2.pat1id
       )
inner
  join pat2
    on pat1pat2.pat2id = pat2.id

then tada, nice results:

pat1descr pat2descr
foo two
foo three
bar one
bar three
qux one
qux two
qux three

explanation? parentheses are required for multiple joins!!!