LEFT JOIN SQL Join Expression not supported
12,723
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;
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;
Author by
Bill Peet
Updated on June 19, 2022Comments
-
Bill Peet almost 2 years
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;
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'.
Any ideas?