6

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?

Bill Peet
  • 477
  • 1
  • 8
  • 23
  • 1
    Might be those unnecessary brackets. – Dan Bracuk Feb 23 '15 at 23:40
  • Everything else I've read seem to indicate these errors happen with not enough brackets! – Bill Peet Feb 23 '15 at 23:41
  • That is just to indicate that the left join is executed first – Bill Peet Feb 23 '15 at 23:41
  • http://stackoverflow.com/questions/16381949/sql-join-expression-not-supported – sqluser Feb 23 '15 at 23:47
  • The brackets are not necessary in order for the query to be semantically and syntactically correct. The query optimizer is virtually always going to do a better job of figuring out the most efficient order of operations (based on indexes and statistics) than you are. – Craig Tullis Feb 23 '15 at 23:47
  • @sqluser how does the article you referenced at http://stackoverflow.com/questions/16381949/sql-join-expression-not-supported relate specifically to this question? – Craig Tullis Feb 23 '15 at 23:49
  • Your left join must be the last in the query. It is a limitation of access – cha Feb 23 '15 at 23:50
  • @cha, thankyou. If I leave the left join last and leave out the brackets, will it still execute that first? – Bill Peet Feb 23 '15 at 23:52

1 Answers1

8

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;
cha
  • 10,301
  • 1
  • 18
  • 26
  • Thanks @cha, if I changed that INNER JOIN in your second suggestion to a RIGHT JOIN, I think this would achieve my desired result – Bill Peet Feb 23 '15 at 23:59
  • Oh, now another problem. It seems I can't put the RIGHT JOIN last? – Bill Peet Feb 24 '15 at 00:03
  • Looking at your query I can see that two LEFT joins should be more appropriate for your situation – cha Feb 24 '15 at 00:24
  • I need to include all records in the PNLTERM table. As far as I can work out, this can only be achieved with a RIGHT join, or my first method of putting a LEFT join first? – Bill Peet Feb 24 '15 at 00:33