左联接适用于表，但查询失败 [英] Left Join works with table but fails with query
问题描述
MS Access 2007中的以下左联接查询
The following left join query in MS Access 2007
SELECT
Table1.Field_A,
Table1.Field_B,
qry_Table2_Combined.Field_A,
qry_Table2_Combined.Field_B,
qry_Table2_Combined.Combined_Field
FROM Table1
LEFT JOIN qry_Table2_Combined
ON (Table1.Field_A = qry_Table2_Combined.Field_A)
AND (Table1.Field_B = qry_Table2_Combined.Field_B);
我希望
返回此结果:
is expected by me to return this result:
+--------+---------+---------+---------+----------------+
|Field_A | Field_B | Field_A | Field_B | Combined_Field |
+--------+---------+---------+---------+----------------+
|1 | | | | |
+--------+---------+---------+---------+----------------+
|1 | | | | |
+--------+---------+---------+---------+----------------+
|2 |1 |2 |1 |John, Doe |
+--------+---------+---------+---------+----------------+
|2 |2 | | | |
+--------+---------+---------+---------+----------------+
[Table1]有4条记录，[qry_Table2_Combined]有1条记录.
[Table1] has 4 records, [qry_Table2_Combined] has 1 record.
但是它给了我这个
+--------+---------+---------+---------+----------------+
|Field_A | Field_B | Field_A | Field_B | Combined_Field |
+--------+---------+---------+---------+----------------+
|2 |1 |2 |1 |John, Doe |
+--------+---------+---------+---------+----------------+
|2 |2 |2 | |, |
+--------+---------+---------+---------+----------------+
真正奇怪的是[Combined_Field]在第二行中有一个逗号.我用逗号连接[qry_Table2_Combined]中的两个字段.
Really weird is that the [Combined_Field] has a comma in the second row. I use a comma to concatenate two fields in [qry_Table2_Combined].
如果左联接查询使用从查询[qry_Table2_Combined]中创建的表，则它将按预期工作.
If the left join query uses a table created from the query [qry_Table2_Combined] it works as expected.
为什么这个左联接查询不能为查询和表提供相同的结果?以及如何使用左联接中的查询获得正确的结果?
Why does this left join query not give the same result for a query and a table? And how can i get the right results using a query in the left join?
推荐答案
串联:将&运算符更改为+运算符，结果应与预期的一样.
Concatenation: change the & operators to + operators and the result should be as expected.
缺少行:我可以重现此问题，但无法解释它，除了说a)可能是一个错误，以及b)可能永远无法修复:(
Missing rows: I can reproduce this issue but cannot explain it, other than to say a) it's probably a bug and b) it will probably never get fixed :(
为了理智起见，我在SQL Server中测试了相同的代码，并且按预期工作.
For sanity I tested the same code in SQL Server and it works as expected.
一般来说，可以使用联合并填充缺失值来模拟外部联接，例如伪代码:
As a general point an outer join can be simulated using union and padding the missing values e.g. pseudo code:
( A JOIN B )
UNION
( A NOT MATCH B { A.*, <pad values for B> } )
在您的情况下以及在Access SQL中:
In your case and in Access SQL:
SELECT Table1.Field_A, Table1.Field_B,
qry_Table2_Combined.Field_A,
qry_Table2_Combined.Field_B,
qry_Table2_Combined.Combined_Field
FROM Table1
INNER JOIN qry_Table2_Combined
ON (Table1.Field_A = qry_Table2_Combined.Field_A)
AND (Table1.Field_B = qry_Table2_Combined.Field_B)
UNION ALL
SELECT Table1.Field_A, Table1.Field_B,
NULL AS Field_A,
NULL AS Field_B,
NULL AS Combined_Field
FROM Table1
WHERE NOT EXISTS ( SELECT *
FROM qry_Table2_Combined
WHERE (Table1.Field_A = qry_Table2_Combined.Field_A)
AND (Table1.Field_B = qry_Table2_Combined.Field_B) );
以上内容似乎产生了您期望的结果.
The above seems to produce the results you were expecting.
访问repro代码，并连接修复，取消注释代码以提供建议的解决方法:
Access repro code, with concatenation fix, uncomment code for suggested workaround:
Sub EXfewfTempler()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = "CREATE TABLE Table1 ( Field_A VARCHAR(10), Field_B VARCHAR(10) );"
.Execute Sql
Sql = "CREATE TABLE Table2 ( Field_B VARCHAR(10), Col_1 VARCHAR(10), Col_2 VARCHAR(10));"
.Execute Sql
Sql = "CREATE VIEW qry_Table2_Combined AS SELECT '2' AS Field_A, Table2.Field_B, Table2.Col_1 + ', ' + Table2.Col_2 AS Combined_Field FROM Table2; "
.Execute Sql
Sql = "INSERT INTO Table1 VALUES (1, NULL);"
.Execute Sql
Sql = "INSERT INTO Table1 VALUES (1, NULL);"
.Execute Sql
Sql = "INSERT INTO Table1 VALUES (2, 1);"
.Execute Sql
Sql = "INSERT INTO Table1 VALUES (2, 2);"
.Execute Sql
Sql = "INSERT INTO Table2 VALUES (1, 'John', 'Doe');"
.Execute Sql
Sql = _
"SELECT " & _
"Table1.Field_A, " & _
"Table1.Field_B, " & _
"qry_Table2_Combined.Field_A, " & _
"qry_Table2_Combined.Field_B, " & _
"qry_Table2_Combined.Combined_Field " & _
"FROM Table1 " & _
"LEFT JOIN qry_Table2_Combined " & _
" ON (Table1.Field_A = qry_Table2_Combined.Field_A) " & _
"AND (Table1.Field_B = qry_Table2_Combined.Field_B);"
' Sql = _
' "SELECT Table1.Field_A, Table1.Field_B, " & _
' " qry_Table2_Combined.Field_A, " & _
' " qry_Table2_Combined.Field_B, " & _
' " qry_Table2_Combined.Combined_Field " & _
' " FROM Table1 " & _
' " INNER JOIN qry_Table2_Combined " & _
' " ON (Table1.Field_A = qry_Table2_Combined.Field_A) " & _
' " AND (Table1.Field_B = qry_Table2_Combined.Field_B) " & _
' "UNION ALL " & _
' "SELECT Table1.Field_A, Table1.Field_B, " & _
' " NULL AS Field_A, " & _
' " NULL AS Field_B, " & _
' " NULL AS Combined_Field " & _
' " FROM Table1 " & _
' " WHERE NOT EXISTS ( SELECT * " & _
' " FROM qry_Table2_Combined " & _
' " WHERE (Table1.Field_A = qry_Table2_Combined.Field_A) " & _
' " AND (Table1.Field_B = qry_Table2_Combined.Field_B) );"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString(2, , vbTab & vbTab, , "<NULL>")
End With
Set .ActiveConnection = Nothing
End With
End Sub
这篇关于左联接适用于表，但查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
