LEFT JOIN不从MS Access中的左表返回完整行? [英] LEFT JOIN in not returning full rows from left table in MS Access?
问题描述
测试框表
---------------
ID测试框
1 TC-1
2 TC-5
3 TC-8
测试表
-----------
ID TestCaseID结果发布
1 1 OK 1.1.111
2 3 FAIL 1.1.111
测试案例结果
TC-1 OK
TC-5< empty>
TC-8 FAIL
我得到的是
测试案例结果
TC-1 OK
TC-8 FAIL
查询:
SELECT Testcases.Testcase,Tests.Result
FROM Testcases LEFT JOIN Tests ON Testcases.ID = Tests.TestCaseID
WHERE Tests.Release =1.1.111;
:
SELECT Testcases.Testcase
,Tests.Result
FROM Testcases
LEFT JOIN测试
ON((Testcases.ID = Tests.TestCaseID)
AND(Tests.Release =1.1.111)
)
和:
SELECT Testcases.Testcase
, Tests.Result
FROM Testcases
LEFT JOIN测试
ON Testcases.ID = Tests.TestCaseID
WHERE Tests.Release =1.1.111
OR Tests.TestCaseID IS NULL
在表中再插入一行 Testcases
, ID = 4,Testcase = 20
和表中的行测试
,使用 TestCaseID = 4 Result =WhateverRelease =2.2.37
查看两个选项之间的区别。
简而言之,第一个查询将显示所有Testscase,结果仅显示在具有 Release =1.1.111
第二个将只显示具有 Release =1.1.111的测试的Testscase
注意:第一个查询不能在Access您可以将其保存在SQL模式,但似乎如果您关闭并重新打开它,Access会删除一些括号由于未知的原因。
它(第一个查询)也可以写成:
SELECT Testcases.Testcase
,g.Result
FROM Testcases
LEFT JOIN
(SELECT *
FROM Tests
WHERE Tests.Release =1.1.111)
)
AS g
ON(Testcases.ID = g.TestCaseID)
或
SELECT Testcases.TestCase
,结果
FROM Testcases
INNER JOIN测试
ON(Testcases.ID = Tests.TestCaseID)
WHERE(Tests.Release =1.1.111)
UNION ALL
SELECT Testcases.TestCase,NULL
FROM Testcases
WHERE NOT EXISTS
(SELECT 1
FROM Tests
WHERE Testcases.ID = Tests.TestCaseID)
AND(Tests.Release =1.1.111)
)
或更好(因为它可以在设计模式下显示):
SELECT Testcases.Testcase
,IIf((Tests.Release =1.1.111),Tests.Result,Null)
结果
FROM Testcases
LEFT JOIN测试
ON Testcases .ID = Tests.TestCaseID
GROUP BY Testcases.Testcase
,IIf((Tests.Release =1.1.111),Tests.Result,Null)
Testcases table
---------------
ID Testcase
1 TC-1
2 TC-5
3 TC-8
Tests table
-----------
ID TestCaseID Result Release
1 1 OK 1.1.111
2 3 FAIL 1.1.111
What I want to get is
Testcase Result
TC-1 OK
TC-5 <empty>
TC-8 FAIL
What I get is
Testcase Result
TC-1 OK
TC-8 FAIL
Query:
SELECT Testcases.Testcase, Tests.Result
FROM Testcases LEFT JOIN Tests ON Testcases.ID=Tests.TestCaseID
WHERE Tests.Release="1.1.111";
There are two (subtly) different ways to do that:
SELECT Testcases.Testcase
, Tests.Result
FROM Testcases
LEFT JOIN Tests
ON ( ( Testcases.ID = Tests.TestCaseID )
AND ( Tests.Release = "1.1.111" )
)
and:
SELECT Testcases.Testcase
, Tests.Result
FROM Testcases
LEFT JOIN Tests
ON Testcases.ID = Tests.TestCaseID
WHERE Tests.Release = "1.1.111"
OR Tests.TestCaseID IS NULL
Insert one more row into table Testcases
, with ID=4, Testcase=20
and a row into table Tests
, with TestCaseID=4 Result="Whatever" Release="2.2.37"
to see the difference between the 2 options.
In short, first query will show all Testscases, with results shown only for tests having Release="1.1.111"
, the rest testcases will show Results as empty (NULL).
The second will show only Testscases having tests with Release="1.1.111"
. And also all Testcase without any test.
Note: The 1st query cannot be shown in Access' "Design" mode. You can save it in SQL mode but it appears that if you close and reopen it, Access erases some parentheses for unknown reasons. You can still run it though.
It (1st query) can also be written as:
SELECT Testcases.Testcase
, g.Result
FROM Testcases
LEFT JOIN
( SELECT *
FROM Tests
WHERE ( Tests.Release = "1.1.111" )
)
AS g
ON ( Testcases.ID = g.TestCaseID )
or
SELECT Testcases.TestCase
, Tests.Result
FROM Testcases
INNER JOIN Tests
ON ( Testcases.ID = Tests.TestCaseID )
WHERE ( Tests.Release = "1.1.111" )
UNION ALL
SELECT Testcases.TestCase, NULL
FROM Testcases
WHERE NOT EXISTS
( SELECT 1
FROM Tests
WHERE ( Testcases.ID = Tests.TestCaseID )
AND ( Tests.Release = "1.1.111" )
)
or even better (because it can be shown in Design mode):
SELECT Testcases.Testcase
, IIf((Tests.Release="1.1.111"), Tests.Result, Null)
AS Result
FROM Testcases
LEFT JOIN Tests
ON Testcases.ID = Tests.TestCaseID
GROUP BY Testcases.Testcase
, IIf((Tests.Release="1.1.111"), Tests.Result, Null)
这篇关于LEFT JOIN不从MS Access中的左表返回完整行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!