完全外连接未返回所有表的内容

2022-03-13 00:00:00 join sql sql-server inner-join outer-join

我有下面提到的3个表。

表

错误

Id | Name | Other information ...
1  | Bug1 | ...
2  | Bug2 | ...
3  | Bug3 | ...
4  | Bug4 | ...
5  | Bug5 | ...

链接

Id  | Test_id | Bug_id
100 | 1000    | 4
101 | 1100    | 2
102 | 1200    | 2
103 | 1200    | 5

测试

Id   | Name | Other information ...
1000 | TC1  | ...
1100 | TC2  | ...
1200 | TC3  | ...
1300 | TC4  | ...
1400 | TC5  | ...
1500 | TC6  | ...

请求

SELECT Bug.Id, Bug.Name, Test.Id, Test.Name
FROM Test FULL OUTER JOIN Link ON Link.Test_id = Test.Id
FULL OUTER JOIN Bug ON Link.Bug_id = Bug.Id
INNER JOIN Test_Detail ON Test_Detail.Test_id = Test.Id
INNER JOIN Release ON Release.Id = Test.Release_id
INNER JOIN Cycle ON Cycle.Release_id = Release.Id

但是，这没有提供预期的结果.

预期结果

如果有链接，我希望获得有关该错误的信息，并测试
如果有没有链接的bug，我想有关错误的信息
如果有没有链接的测试，我想了解有关该测试的信息

结果

BugId | BugName | TestId | TestName
1     | Bug1    |
2     | Bug2    | 1100   | TC2
2     | Bug2    | 1200   | TC3
3     | Bug3    |
4     | Bug4    | 1000   | TC1
5     | Bug5    | 1200   | TC2
      |         | 1300   | TC4
      |         | 1400   | TC5
      |         | 1500   | TC6

结论

FULL OUTER JOIN不是要使用的正确联接吗？您能帮我理解一下为什么它没有按预期工作吗？

谢谢

编辑%1

基于Charlieface答案的结果。

我正在获取错误、测试和链接。但我只需要链接，如果它存在的话。如果没有链接，我需要测试和错误详细信息。如上面预期结果部分所述。

BugId | BugName | TestId | TestName
      |         | 1100   | TC2
      |         | 1200   | TC3
2     | Bug2    |        | 
2     | Bug2    | 1100   | TC2
2     | Bug2    | 1200   | TC3

编辑%2

完整请求

(TESTCYCL在此帖子中为TEST)

SELECT DISTINCT
B.BG_BUG_ID
, B.BG_STATUS
, B.BG_SUMMARY
, B.BG_USER_TEMPLATE_13
, B.BG_SEVERITY
, B.BG_PRIORITY
, B.BG_USER_TEMPLATE_10
, B.BG_USER_TEMPLATE_08
, R.REL_NAME
, RC2.RCYC_NAME
, B.BG_DETECTION_DATE
, B.BG_RESPONSIBLE
, B.BG_USER_TEMPLATE_03
, B.BG_USER_03
, B.BG_CLOSING_DATE
, B.BG_USER_TEMPLATE_19
, B.BG_USER_TEMPLATE_18
, B.BG_DETECTED_BY
, TC.TC_TESTCYCL_ID
, TC.TC_TEST_ID
, T.TS_NAME
, TC.TC_STATUS
, T.TS_USER_TEMPLATE_05
, CF.CF_ITEM_NAME
, C.CY_CYCLE
, RC1.RCYC_NAME
, TC.TC_USER_TEMPLATE_06
, TC.TC_USER_TEMPLATE_05
, TC.TC_USER_TEMPLATE_08
FROM TESTCYCL AS TC
INNER JOIN RELEASE_CYCLES AS RC1 ON TC.TC_ASSIGN_RCYC = RC1.RCYC_ID
INNER JOIN TEST AS T ON TC.TC_TEST_ID = T.TS_TEST_ID
INNER JOIN CYCLE AS C ON TC.TC_CYCLE_ID = C.CY_CYCLE_ID
INNER JOIN CYCL_FOLD AS CF ON C.CY_FOLDER_ID = CF.CF_ITEM_ID
FULL OUTER JOIN LINK AS L ON L.LN_ENTITY_ID = TC.TC_TESTCYCL_ID
FULL OUTER JOIN BUG AS B
INNER JOIN RELEASE_CYCLES AS RC2 ON B.BG_DETECTED_IN_RCYC = RC2.RCYC_ID
INNER JOIN RELEASES AS R ON B.BG_DETECTED_IN_REL = R.REL_ID
ON L.LN_BUG_ID = B.BG_BUG_ID

解决方案

您需要重新排序联接。

将与一个表相关的所有inner join与该表放在一起。：

SELECT Bug.Id, Bug.Name, Test.Id, Test.Name
FROM Test
INNER JOIN Test_Detail ON Test_Detail.Test_id = Test.Id
INNER JOIN Release ON Release.Id = Test.Release_id
INNER JOIN Cycle ON Cycle.Release_id = Release.Id
FULL OUTER JOIN Link ON Link.Test_id = Test.Id
FULL OUTER JOIN Bug ON Link.Bug_id = Bug.Id

假设您也要将某些内容加入Bug。为此，您需要嵌套ON条件：

SELECT Bug.Id, Bug.Name, Test.Id, Test.Name
FROM Test
INNER JOIN Test_Detail ON Test_Detail.Test_id = Test.Id
INNER JOIN Release ON Release.Id = Test.Release_id
INNER JOIN Cycle ON Cycle.Release_id = Release.Id
FULL OUTER JOIN Link ON Link.Test_id = Test.Id
FULL OUTER JOIN Bug
      INNER JOIN Bug_Detail bd ON bd.Bug_id = Bug.id
  ON Link.Bug_id = Bug.Id

请注意，与流行的观点相反，括号()在这里没有什么不同，尽管它们在视觉上很有用。重要的是ON子句的顺序：Bug_detail内联接嵌套在Bug

的完全联接内

相关文章