SQL 根据 MS Access 中的附加条件进行左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2015878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LEFT JOINing on additional criteria in MS Access
提问by Supergibbs
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 查询（一个简单的测试用例）在 MS SQL 中运行良好，但无法在 MS Access (JET-SQL) 中获得等效查询。问题是 LEFT JOIN 中的附加条件。如何在 MS Access 中执行此操作？
T-SQL:
T-SQL：
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.A_ID
AND B.F_ID = 3
JET-SQL (what I have so far but crashes Access!):
JET-SQL（到目前为止我所拥有的但会导致 Access 崩溃！）：
SELECT * FROM dbo_A
LEFT JOIN dbo_B ON (dbo_A.ID = dbo_B.A_ID AND dbo_B.F_ID = 3)
回答by David-W-Fenton
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;
If you're running Access with "SQL 92" compatibility mode turned on, you can do the more standard:
如果您在打开“SQL 92”兼容模式的情况下运行 Access，则可以执行更标准的操作：
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;
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 中进行编辑吗？如果没有，只需使用原生 T-SQL 的直通查询。如果是这样，我可能会为此创建一个服务器端视图，如果文字值是您要参数化的值（即 F_ID=3 实际上是 F_ID=N 其中 N是在运行时选择的值）。
BTW, I write these subselect derived table SQL statements every single day while working in Access. It's not that big a deal.
顺便说一句，我每天都在使用 Access 编写这些子选择派生表 SQL 语句。这没什么大不了的。
回答by Tom H
Do you get an error message when it crashes or does it just lock up? Judging by the dbo_B name I'm going to guess that these are linked tables in Access. I believe that when you do a join like that Access doesn't tell SQL server that it needs the result of the join, it says, "Give me all of the rows of both tables" then it tries to join them itself. If the tables are very large this can cause the application to lock up.
您在崩溃时收到错误消息还是只是锁定？从 dbo_B 名称来看，我猜这些是 Access 中的链接表。我相信，当你进行这样的连接时，Access 不会告诉 SQL 服务器它需要连接的结果，它会说，“给我两个表的所有行”，然后它会尝试自己连接它们。如果表非常大，这可能会导致应用程序锁定。
You're probably better off creating a view on SQL Server for what you need.
您最好根据需要在 SQL Server 上创建视图。
回答by Morteza ebrahim nejad
I think ms access expect to both tables name in each section of Joins ON clause. As a trick this work for me:
我认为 ms access 期望在 Joins ON 子句的每个部分中使用两个表名称。作为一个技巧，这对我有用：
SELECT * FROM A
LEFT OUTER JOIN B ON A.ID = B.A_ID
AND B.F_ID = IIF(True, 3, A.ID)
A.ID or any other else field from table A
A.ID 或表 A 中的任何其他字段
回答by Melvin
That last condition technically isn't a join but a comparison to a literal value. Put it in a WHERE clause:
从技术上讲，最后一个条件不是连接，而是与文字值的比较。把它放在一个 WHERE 子句中：
SELECT *
FROM a LEFT OUTER JOIN b ON a.ID = b.a_id
WHERE b.f_id = 3;
