是否可以在Access的联接条件中使用子查询? [英] Is it possible to use subquery in join condition in Access?

问题描述

在Postgresql中，我可以在联接条件下使用子查询

In postgresql I can use subquery in join condition

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

但是当我尝试在Access中使用它

But when I try to use it in Access

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

我收到语法错误.在Access中实际上是不可能的，还是我的错误?

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

我知道使用WHERE可以获得相同的结果，但是我的问题是JOIN在Access中的可能性.

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

推荐答案

根据

语法

从表1 [左|右] JOIN table2 ON table1.field1 compopr table2.field2

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

然后(强调我的意思):

And (emphasis mine):

field1，field2:所连接的字段的名称.这些字段必须具有相同的数据类型并且包含相同类型的数据，但是它们不必具有相同的名称.

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

这篇关于是否可以在Access的联接条件中使用子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！