Can I use a subquery in connection state in Access?

Question

In postgresql, I can use a subquery in a 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 a syntax error. Is this really not possible in Access or just my mistake?

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

Answer 1

This is not possible, for the MSDN documentation :

Syntax

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

And (my attention):

field1, field2: The names of the fields that have been merged. Fields must be of the same data type and contain the same data type, but they must not have the same name,

It looks like you can't even have hardcoded values ​​in your connection; you must specify the column name to join.

In your case, you need:

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