不支持LEFT JOIN SQL连接表达式 [英] LEFT JOIN SQL Join Expression not supported

问题描述

我找不到此SQL表达式出了什么问题

I can't find what is wrong with this SQL expression:

SELECT DISTINCT PNLTERM.TAGSTRIP, TERMS.TERM, PNLTERM.STRIPSEQ, TERMS.WIRENO, 
TERMS.REF, TERMS.MFG, TERMS.CAT, TERMS.BLOCK, PNLTERM.HDL, TERMS.WDBLKNAM, 
TERMS.JUMPER_ID, WIREFRM2.WIRELAY
FROM (PNLTERM LEFT JOIN TERMS ON PNLTERM.LINKTERM = TERMS.LINKTERM) 
INNER JOIN WIREFRM2 ON TERMS.HDL = WIREFRM2.COMP_HDL;

这与PNLTERM/TERMS上的左连接有关；因为它适用于内部联接和右联接，但不适用于左联接. 错误是不支持联接表达式".

It is something to do with the left join on PNLTERM/TERMS; as it works with a inner and right join but not with a left join. The error is 'Join expression not supported'.

有什么想法吗?

推荐答案

MS Access具有一些有关将LEFT/RIGHT联接与INNER联接一起使用的规则.具体来说，有一个关于其混合的规则.在此处进行解释:

MS Access has some rules regarding its use of LEFT/RIGHT joins with the INNER joins. Specifically, there is a rule regarding its mix. It is explained here:

左联接"或右联接"可以嵌套在内部联接"内部，但是内联接"可以不嵌套在左联接"或右联接"内部.

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.

您有两种选择:您可以将第二个联接转换为左联接:

You have a couple of options: You can convert the second join to a left join:

SELECT DISTINCT PNLTERM.TAGSTRIP, TERMS.TERM, PNLTERM.STRIPSEQ, TERMS.WIRENO, 
TERMS.REF, TERMS.MFG, TERMS.CAT, TERMS.BLOCK, PNLTERM.HDL, TERMS.WDBLKNAM, 
TERMS.JUMPER_ID, WIREFRM2.WIRELAY
FROM (PNLTERM LEFT JOIN TERMS ON PNLTERM.LINKTERM = TERMS.LINKTERM) 
LEFT JOIN WIREFRM2 ON TERMS.HDL = WIREFRM2.COMP_HDL;

或者，您可以更改查询，以便首先使用INNER联接:

Or, you can change the query, so that the INNER join comes first:

SELECT DISTINCT PNLTERM.TAGSTRIP, TERMS.TERM, PNLTERM.STRIPSEQ, TERMS.WIRENO, 
TERMS.REF, TERMS.MFG, TERMS.CAT, TERMS.BLOCK, PNLTERM.HDL, TERMS.WDBLKNAM, 
TERMS.JUMPER_ID, WIREFRM2.WIRELAY
FROM (WIREFRM2 INNER JOIN TERMS ON TERMS.HDL = WIREFRM2.COMP_HDL) 
RIGHT JOIN PNLTERM ON PNLTERM.LINKTERM = TERMS.LINKTERM;

这篇关于不支持LEFT JOIN SQL连接表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！