#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Location
    Toronto, Canada
    Posts
    14
    Rep Power
    0

    MS Access - More than 2 LEFT JOINs


    Hello,

    Sorry if this post is in the wrong place (I couldn't find a place for Access threads).

    I am using ASP (VBScript) with an MS Access database. I understand Access expects parenthesis for SQL queries that contain multiple OUTER JOINs. For example, with 2 joins, this works:

    Code:
    objCommand.CommandText = 
    "SELECT field1_name, field2_name 
    FROM (Table1 
    LEFT OUTER JOIN Table2 ON Table2.field1_id = Table1.field1_id) 
    LEFT OUTER JOIN Table3 ON Table3.field2_id = Table1.field2_id;"
    However, when I try to extend this to 3 joins, this doesn't work:
    Code:
    'objCommand.CommandText = 
    "SELECT field1_name, field2_name, field3_name 
    FROM ((Table1 
    LEFT OUTER JOIN Table2 ON Table2.field1_id = Table1.field1_id) 
    LEFT OUTER JOIN Table3 ON Table3.field2_id = Table1.field2_id) 
    LEFT OUTER JOIN Table4 ON Table4.field3_id = Table1.field3_id;"

    The error message I get is the following:
    Code:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'   
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.
    Does anyone have any idea what could be wrong with that syntax? Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by elysianfields44
    Does anyone have any idea what could be wrong with that syntax? Thanks.
    that syntax is fine

    however, i'm willing to bet that your tables aren't actually called "table1" and "table2" and your columns aren't actually called "field1_id" and "field2_id"

    which means that you text-edited your actual query, and inadvertently fixed the error
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo