左加入MS Access [英] LEFT JOINS in MS Access

问题描述

我正在尝试对其他人的MS Access查询进行故障排除，并不断收到无效的操作错误.该帮助似乎并不适用，因为我只是在运行查询.一切都作为INNER JOINS起作用，但是当我切换回LEFT JOIN时出现错误.

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

---

我可以得到一个级别的LEFT JOIN，但是每次添加第二个LEFT JOIN时，都会弹出错误消息.

---

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

推荐答案

混合使用INNER和LEFT联接时，Access的db引擎经常出现故障.如果该查询在没有最后一个内部联接的情况下正常工作...

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

...，那么您可以尝试将该部分作为子查询，并内部连接lines_qry到该子查询.它可能会超过错误.

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

如果除orders之外的任何其他表都包含一个名为CUST_ORDER_ID的字段，则在子查询中您将需要除SELECT *之外的其他内容，以避免产生歧义.

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.

这篇关于左加入MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！