CODEWITHSUNDEEP
sqlms-access
CodeMed
CodeMed

Reputation: 9191

syntax error in from clause in nested joins in an ms access

I am getting a Syntax Error in FROM Clause dialog box when I try to run the following query in MS-Access 2010: 

SELECT 
    Contact_addresses.AddressID, 
    Contact_addresses.ContactID, 
    Contact_addresses.Address1, 
    Contact_addresses.Address2, 
    Contact_addresses.City, 
    Contact_addresses.State, 
    Contact_addresses.Zip, 
    Owner_Operator.FIRM_NAME, 
    Official_Correspondent.FIRST_NAME, 
    Official_Correspondent.LAST_NAME, 
    Official_Correspondent.SUBACCOUNT_COMPANY_NAME, 
    Official_Correspondent.PHONE_NUMBER
FROM Contact_addresses 
    (
        LEFT JOIN 
            (SELECT
                Owner_Operator.CONTACT_ID, 
                Owner_Operator.FIRM_NAME 
                FROM Owner_Operator) 
            ON Contact_addresses.ContactID=Owner_Operator.CONTACT_ID
        ) 
        LEFT JOIN 
            (SELECT 
                Official_Correspondent.CONTACT_ID, 
                Official_Correspondent.FIRST_NAME, 
                Official_Correspondent.LAST_NAME, 
                Official_Correspondent.SUBACCOUNT_COMPANY_NAME,
                Official_Correspondent.PHONE_NUMBER 
                FROM Official_Correspondent) 
            ON Contact_addresses.ContactID=Official_Correspondent.CONTACT_ID
        ;

When I dismiss the dialog box, access highlights the ( after FROM Contact_addresses.

I know that I need parentheses with multiple nested joins in Access, but can someone please explain the concepts of how this works, in addition to showing how to fix whatever the problem is?

The relevant aspects of the schema of the underlying tables should be clear from the SQL SELECT statements.

Upvotes: 0

Views: 2005

Answers (2)

CodeMed
CodeMed

Reputation: 9191

The problem was in the location of the opening brackets ((. They need to be immediately after the first FROM. Here is what works: 

SELECT Contact_addresses.AddressID, Contact_addresses.ContactID, Contact_addresses.Address1, Contact_addresses.Address2, Contact_addresses.City, Contact_addresses.State, Contact_addresses.Zip, Owner_Operator.FIRM_NAME, Official_Correspondent.FIRST_NAME, Official_Correspondent.LAST_NAME, Official_Correspondent.SUBACCOUNT_COMPANY_NAME, Official_Correspondent.PHONE_NUMBER
FROM ((Contact_addresses 

        LEFT JOIN 
            (SELECT 
                Owner_Operator.CONTACT_ID, 
                Owner_Operator.FIRM_NAME 
                FROM Owner_Operator) AS Owner_Operator 
            ON Contact_addresses.ContactID=Owner_Operator.CONTACT_ID 
)
        LEFT JOIN 
            (SELECT 
                Official_Correspondent.CONTACT_ID, 
                Official_Correspondent.FIRST_NAME, 
                Official_Correspondent.LAST_NAME, 
                Official_Correspondent.SUBACCOUNT_COMPANY_NAME, 
                Official_Correspondent.PHONE_NUMBER 
                FROM Official_Correspondent) AS Official_Correspondent 
            ON Contact_addresses.ContactID=Official_Correspondent.CONTACT_ID 
)        
;

Upvotes: 0

Twelfth
Twelfth

Reputation: 7180

I think this should work, but I lack access to test. General syntax to hold to

From table left join (statement) alias on table.col = alias.col left join ...

Altering your statement to this:

FROM Contact_addresses 

    LEFT JOIN 
        (SELECT
            Owner_Operator.CONTACT_ID, 
            Owner_Operator.FIRM_NAME 
            FROM Owner_Operator)   Owner_Operator
        ON Contact_addresses.ContactID=Owner_Operator.CONTACT_ID

    LEFT JOIN 
        (SELECT 
            Official_Correspondent.CONTACT_ID, 
            Official_Correspondent.FIRST_NAME, 
            Official_Correspondent.LAST_NAME, 
            Official_Correspondent.SUBACCOUNT_COMPANY_NAME,
            Official_Correspondent.PHONE_NUMBER 
            FROM Official_Correspondent)  Official_Correspondent
        ON Contact_addresses.ContactID=Official_Correspondent.CONTACT_ID
    ;

I've added the table alias to match what you call it in your join and I removed the offending set of brackets.

Upvotes: 1

Related Questions