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

code>。还有所有Testcase没有任何测试。

注意：第一个查询不能在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屋！