CODEWITHSUNDEEP
sqlms-accessjoinleft-join
Paul
Paul

Reputation: 43

LEFT JOINS in MS Access

I am trying to troubleshoot someone else's MS Access query and keep getting an invalid operation error. The Help doesn't seem to apply as I am just running a query. It all works as INNER JOINS but when I switch back to the LEFT JOIN the error. 

SELECT *
 FROM ((((orders 
 INNER JOIN orders_customers ON orders.CUST_ORDER_ID = orders_customers.ID) 
 LEFT JOIN quoted_theory ON orders.PART_ID = quoted_theory.PART_ID) 
 LEFT JOIN conversions ON orders.PART_ID = conversions.PART_ID) 
 LEFT JOIN dbo_WO_Header ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number) 
 INNER JOIN lines_qry ON orders.CUST_ORDER_ID = lines_qry.WORKORDER_BASE_ID

I can get one level of LEFT JOIN, but each time I add a second LEFT JOIN the error pops up.

Upvotes: 4

Views: 3985

Answers (2)

garaad
garaad

Reputation: 1

SELECT *
FROM
    (
        SELECT *
        FROM
            (((orders INNER JOIN orders_customers
            ON orders.CUST_ORDER_ID = orders_customers.ID)
            LEFT JOIN quoted_theory
            ON orders.PART_ID = quoted_theory.PART_ID)
            LEFT JOIN conversions
            ON orders.PART_ID = conversions.PART_ID)
            LEFT JOIN dbo_WO_Header
            ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number
    ) AS sub
    INNER JOIN lines_qry
    ON sub.CUST_ORDER_ID = lines_qry.WORKORDER_BASE_ID

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Access' db engine frequently balks when mixing INNER and LEFT joins. If that query works without the last inner join ... 

SELECT *
FROM
    (((orders INNER JOIN orders_customers
    ON orders.CUST_ORDER_ID = orders_customers.ID)
    LEFT JOIN quoted_theory
    ON orders.PART_ID = quoted_theory.PART_ID)
    LEFT JOIN conversions
    ON orders.PART_ID = conversions.PART_ID)
    LEFT JOIN dbo_WO_Header
    ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number

... then you could try that part as a subquery and inner join lines_qry to the subquery. It might get past the error.

SELECT *
FROM
    (
        SELECT *
        FROM
            (((orders INNER JOIN orders_customers
            ON orders.CUST_ORDER_ID = orders_customers.ID)
            LEFT JOIN quoted_theory
            ON orders.PART_ID = quoted_theory.PART_ID)
            LEFT JOIN conversions
            ON orders.PART_ID = conversions.PART_ID)
            LEFT JOIN dbo_WO_Header
            ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number
    ) AS sub
    INNER JOIN lines_qry
    ON sub.CUST_ORDER_ID = lines_qry.WORKORDER_BASE_ID

If any other table besides orders includes a field named CUST_ORDER_ID, you will need something other than SELECT * within the subquery to avoid ambiguity.

Upvotes: 3

Related Questions