来自一个查询的表达式被拉入后续查询 [英] Expression from one query being pulled into subsequent query

问题描述

我有一个下表"Base"和"Join":

I have a the following tables "Base" and "Join":

Base:
ID  Field
A   1
B   2
D   

Join:
ID
A
B
C
D

我使用以下选择查询"ExampleQuery"来解析"Base":

I use the following select query "ExampleQuery" to parse "Base":

SELECT Base.ID, IIf(IsNull([Field]),"None",[Field]) AS Newfield
FROM Base;

以及以下选择查询到左联接表"Join"以查询"ExampleQuery":

And the following select query to left-join table "Join" to query "ExampleQuery":

SELECT Join.ID, ExampleQuery.Newfield
FROM [Join] LEFT JOIN ExampleQuery ON Join.ID = ExampleQuery.ID;

我的输出如下:

ID  Newfield
A   1
B   2
C   None
D   None

我期望C的值为null，因为在ExampleQuery的输出中不存在C，但是它使用的是原始查询中表达式的逻辑.是什么原因导致这种情况发生，如何预防呢?我想将我的初始选择查询严格地视为一个临时表.

I am expecting the value of C to be null since it was not present in the output of ExampleQuery, however it is using the logic from the expression in the original query instead. What causes this to occur, and how can I prevent it? I want to treat my initial select query strictly as a temporary table.

推荐答案

您可以尝试加入一个子查询，该子查询首先用None替换Base表中缺少的字段值:

You can try joining to a subquery which first replaces missing field values in the Base table with None:

SELECT t1.ID, t2.Newfield
FROM [Join] t1
LEFT JOIN
(
    SELECT ID, IIF(ISNULL([Field]),"None", [Field]) AS Newfield
    FROM Base
) t2
    ON t1.ID = t2.ID;

顺便说一句，JOIN是Access(可能还有其他每个SQL版本)中的保留关键字.您应该避免使用关键字来命名表，列等.

By the way, JOIN is a reserved keyword in Access (and probably every other version of SQL). You should avoid naming tables, columns, etc. using keywords.

这篇关于来自一个查询的表达式被拉入后续查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！