Is it possible to use subquery in join condition in Access?

Question

In postgresql I can use subquery in join condition

SELECT * 
FROM table1 LEFT JOIN table2
     ON table1.id1 = (SELECT id2 FROM table2 LIMIT 1);

But when I try to use it in Access

SELECT *
FROM table1 LEFT JOIN table2 
     ON table1.id1 = (SELECT TOP 1 id2 FROM table2);

I get syntax error. Is it actually impossible in Access or just my mistake?

I know that I can get the same result with WHERE, but my question is about possibilities of JOIN in Access.

Solution

It's not possible, per the MSDN documentation:

Syntax

FROM table1 [ LEFT | RIGHT ] JOIN table2 ON table1.field1 compopr table2.field2

And (emphasis mine):

field1, field2: The names of the fields that are joined. The fields must be of the same data type and contain the same kind of data, but they do not need to have the same name.

It appears you can't even have hard-coded values in your join; you must specify the column name to join against.

In your case, you would want:

SELECT *
FROM Table1
LEFT JOIN (
    SELECT DISTINCT TOP 1 ID 
    FROM Table2
    ORDER BY ID
) Table2Derived ON Table1.ID = Table2Derived.ID

Comments

[Matt Donnan]

+1 I'm moving away from sub-queries in almost all instances as derived tables give greater performance in most cases