Pedrochu Pedrochu - 11 days ago 8
SQL Question

MS-Access SQL : Join expression not supported

I have 2 tables A and B. I want to create a third one, C. C must contain each record that is in A but not in B, and each record that is in A and B.

I've tried the following :

SELECT A.* INTO C FROM (A INNER JOIN B ON A.Id = B.Id) LEFT JOIN B ON A.Id = B.Id WHERE B.Id IS NULL;


But it gives me the error message :
JOIN expression not supported.


When there's only the
INNER JOIN
or the
LEFT JOIN
, it works perfectly. But for some reason when I combine both with the brackets, it doesn't work.

I believe I am using MS-Access 2013, if that helps.
By the way, I'm an Access and an SQL newbie.

Answer

The correct logic is:

SELECT A.* INTO C
FROM A LEFT JOIN
     B
     ON A.Id = B.Id
WHERE B.Id IS NULL;

You do not need two joins. My guess is that the problem with your query is that B appears twice in the FROM clause, without a table alias. MS Access doesn't know what the second B refers to.