左加入MS Access中的其他条件 [英] LEFT JOINing on additional criteria in MS Access

问题描述

我有以下在MS SQL中运行良好的T-SQL查询(一个简单的测试用例)，但是在MS Access(JET-SQL)中无法获得等效的查询.问题是左联接中的附加条件. 如何在MS Access中做到这一点?

I have the following T-SQL query (a simple test case) running fine in MS SQL but cannot get the equivalent query in MS Access (JET-SQL). The problem is the additional criteria in the LEFT JOIN. How can I do this in MS Access?

T-SQL:

SELECT * FROM A 
LEFT OUTER JOIN B ON A.ID = B.A_ID 
                 AND B.F_ID = 3

JET-SQL(到目前为止，我所拥有的只是使Access崩溃了！):

JET-SQL (what I have so far but crashes Access!):

SELECT * FROM dbo_A 
LEFT JOIN dbo_B ON (dbo_A.ID = dbo_B.A_ID AND dbo_B.F_ID = 3)

推荐答案

您需要使用子选择来应用条件:

You need to use a subselect to apply the condition:

  SELECT *
  FROM dbo_A LEFT JOIN 
    [SELECT dbo_B.* FROM dbo_B WHERE dbo_B.F_ID = 3]. AS dbo_B 
      ON dbo_A.ID = dbo_B.A_ID;

如果您在启用"SQL 92"兼容模式的情况下运行Access，则可以执行更标准的操作:

If you're running Access with "SQL 92" compatibility mode turned on, you can do the more standard:

  SELECT *
  FROM dbo_A LEFT JOIN 
    (SELECT dbo_B.* FROM dbo_B WHERE dbo_B.F_ID = 3) AS dbo_B 
      ON dbo_A.ID = dbo_B.A_ID;

您需要在Access中对其进行编辑吗?如果没有，只需对本机T-SQL使用传递查询.如果是这样，我可能会为此创建一个服务器端视图，如果文字值是您要参数化的值(即F_ID = 3实际上是F_ID = N，其中N是在运行时选择的值.

Do you need this to be editable in Access? If not, just use a passthrough query with the native T-SQL. If so, I would likely create a server-side view for this, and I'd especially want to move it server-side if the literal value is something you would parameterize (i.e., the F_ID=3 is really F_ID=N where N is a value chosen at runtime).

顺便说一句，我每天在Access中工作时都会编写这些子选择派生表SQL语句.没什么大不了的.

BTW, I write these subselect derived table SQL statements every single day while working in Access. It's not that big a deal.

这篇关于左加入MS Access中的其他条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！