左加入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屋!