Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Posts
    20

    Unanswered: SQL Left join with select criteria

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

    http://www.dbforums.com/showthread.php?p=4168806

    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".

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2004
    Posts
    20
    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
    Yep, that did it for me and now it works great! thanks alot guys for all the help!



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •