3

I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed by the ON syntax when removing that (the latter for clearer reasons based on prior knowledge of SQL JOIN ON syntax).

Here is the code:

SELECT Column1, Column2, Table1.Column3
FROM Table1 
JOIN Table2
    ON Table1.Column3 = Table2.Column3
   AND Column4 = '{NAME}'
ORDER BY Column4;

Is there something incorrect about this syntax? By the way, this is using ANSI-89 Syntax.

  • I tested INNER JOIN with this code after changing the code back to its original names, and it works with that. – JMK Sep 2 '16 at 20:14
  • if you found the solution to your problem, add an answer. You will be able to accept your own answer after a couple of days. It will be easier for others to find the solution if it's an answer, instead of a comment – Lennart Sep 2 '16 at 20:24
  • 2
    Okay, I think I should do that. – JMK Sep 2 '16 at 20:33
  • I should clarify that I have Access 2016 set so that it accepts ANSI-89 Syntax. – JMK Sep 3 '16 at 14:57
2

Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.

2

The solution I realized needed the INNER JOIN syntax rather than JOIN ON syntax for Access 2016 as JOIN ON without a combination of INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, etc. does not work with this DBMS (Database Management System). Further, I changed the AND logical operator to a WHERE clause as JOIN ON syntax followed simply by AND does not run in Access 2016. Furthermore, the GROUP BY syntax goes beyond the minimum requirements for this query, so I removed it. Therefore, the solution to my question is as follows:

SELECT Column1, Column2, Table1.Column3

FROM Table1 

INNER JOIN Table2

ON Table1.Column3 = Table2.Column3

WHERE Column4 = '{NAME}';
  • 1
    Good feedback, well done! – Lennart Sep 5 '16 at 17:20
  • I think you are wrong about the AND part. I think Access allows ON (condition AND condition2)`. It may need parentheses though. – ypercubeᵀᴹ Sep 5 '16 at 17:49
  • Your proposal is syntactically different than mine was because the code after the ON of the INNER JOIN syntax would be considered a single statement. Your code suggestion works equivalently, but the AND in my original code was after the INNER JOIN ON syntax. AND being a logical operator works between two data entries. Therefore, as I understand it, saying AND at the beginning of a SQL statement is syntactically incorrect. Hence, my original code would not run even with INNER JOIN – JMK Sep 6 '16 at 20:20
  • It certainly would not run if you didn't try it. If you tried it, it would run ;) – ypercubeᵀᴹ Oct 8 '16 at 21:11
  • I think that is missing the point of my response in that your suggestion did not apply to the context of my requirements. Whether it worked is not as important as its relevance to the SQL requirement at that time. – JMK Oct 8 '16 at 22:08
-2

After this passage ON Table1.Column3 = Table2.Column3 you should put where instead of and.

  • You are correct. I did that as well. Having said that, the JOIN ON syntax does not run without the INNER syntax – JMK Sep 2 '16 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.