如何组合左连接？

发布于 2024-10-31 23:24:40 字数 305 浏览 0 评论 0原文

在 MS Access 2007 查询中，如何组合以下 2 个查询？

SELECT Tests.Release, Testcases.TestCase, Tests.Result
FROM Tests LEFT JOIN Testcases ON Tests.TestCaseID=Testcases.ID;

SELECT Tests.Release, ResultEnums.Result
FROM Tests LEFT JOIN ResultEnums ON ResultEnums.ID=Tests.Result;

谢谢

原文

In MS Access 2007 query, how can I combine the following 2 queries?

SELECT Tests.Release, Testcases.TestCase, Tests.Result
FROM Tests LEFT JOIN Testcases ON Tests.TestCaseID=Testcases.ID;

SELECT Tests.Release, ResultEnums.Result
FROM Tests LEFT JOIN ResultEnums ON ResultEnums.ID=Tests.Result;

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

兲鉂ぱ嘚淚 2024-11-07 23:24:40

Access 有这个糟糕的“功能”，它要求您将联接配对在括号中：

Select Tests.Release, Testcases.TestCase, Tests.Result, ResultEnums.Result
From (Tests
    Left Join TestCases
        On TestCases.TestCaseID = Tests.TestCaseID)
    Left Join ResultEnums
        On ResultEnums.ID = Tests.Result

这种类型的查询可能会产生一些不好的结果，具体取决于架构。如果对于给定的 Tests 行，返回多个 TestCases 行和多个 ResultEnums 行，则这些行将通过叉积相互组合。例如，如果给定的 Tests 行返回 5 个 TestCases 行和 3 个 ResultEnums 行，则您将获得该 Tests 行的 15 行。但是，如果 ResultEnums 是 Tests 表的父级（因此对于每一 Tests 行，您只会获得一个 ResultEnums值），那么这应该可以正常工作。

Access has this craptastic "feature" which requires you pair your joins in parentheses:

Select Tests.Release, Testcases.TestCase, Tests.Result, ResultEnums.Result
From (Tests
    Left Join TestCases
        On TestCases.TestCaseID = Tests.TestCaseID)
    Left Join ResultEnums
        On ResultEnums.ID = Tests.Result

This type of query can produce some bad results depending on the schema. If for a given Tests row, multiple TestCases rows and multiple ResultEnums rows are returned, those rows will be combined via a cross product with each other. For example, if a given Tests row returned five TestCases rows and three ResultEnums rows, you will get 15 rows for that Tests row. However, if say ResultEnums is parent to the Tests table (thus for every Tests row, you would only get one ResultEnums value), then this should work fine.

回复收藏 0 原文

丶视觉 2024-11-07 23:24:40

我现在无法测试，但你肯定需要括号：

SELECT Tests.Release
     , Testcases.TestCase
     , Tests.Result
     , ResultEnums.Result
FROM ( Tests 
       LEFT JOIN Testcases
         ON Tests.TestCaseID=Testcases.ID
     ) 
     LEFT JOIN ResultEnums
       ON ResultEnums.ID=Tests.Result 
;

或

SELECT Tests.Release
     , Testcases.TestCase
     , Tests.Result
     , ResultEnums.Result
FROM ( Tests 
       LEFT JOIN ResultEnums
         ON ResultEnums.ID=Tests.Result
     )
     LEFT JOIN Testcases
       ON Tests.TestCaseID=Testcases.ID
;

I can't test now but you surely need parenthesis:

SELECT Tests.Release
     , Testcases.TestCase
     , Tests.Result
     , ResultEnums.Result
FROM ( Tests 
       LEFT JOIN Testcases
         ON Tests.TestCaseID=Testcases.ID
     ) 
     LEFT JOIN ResultEnums
       ON ResultEnums.ID=Tests.Result 
;

SELECT Tests.Release
     , Testcases.TestCase
     , Tests.Result
     , ResultEnums.Result
FROM ( Tests 
       LEFT JOIN ResultEnums
         ON ResultEnums.ID=Tests.Result
     )
     LEFT JOIN Testcases
       ON Tests.TestCaseID=Testcases.ID
;

回复收藏 0 原文

冷夜 2024-11-07 23:24:40

一个非常天真的/简化的解决方案是简单地UNION ALL它们：

SELECT Tests.Release, Testcases.TestCase, Tests.Result
FROM Tests LEFT JOIN Testcases ON Tests.TestCaseID=Testcases.ID;

UNION ALL
SELECT Tests.Release, NULL, ResultEnums.Result
FROM Tests LEFT JOIN ResultEnums ON ResultEnums.ID=Tests.Result;

编辑：更新在这里评论

我想这就是你想要的，来自 ResultEnums 表的 Result，从Tests表中释放，并从Testcases表中关联TestCase，全部JOIN在他们的钥匙上：

SELECT
    re.Result
    ,t.Release
    ,tc.TestCase
FROM
    Tests t
    LEFT JOIN TestCases tc ON t.TestCaseID = tc.ID
    LEFT JOIN ResultEnums re ON re.ID = t.Result

A very naive/simplified solution would be to simply UNION ALL them:

SELECT Tests.Release, Testcases.TestCase, Tests.Result
FROM Tests LEFT JOIN Testcases ON Tests.TestCaseID=Testcases.ID;

UNION ALL
SELECT Tests.Release, NULL, ResultEnums.Result
FROM Tests LEFT JOIN ResultEnums ON ResultEnums.ID=Tests.Result;

edit: update for comment here

I think this is what you want, the Result from the ResultEnums table, the Release from the Tests table, and the associated TestCase from the Testcases table, all JOINed on their keys:

SELECT
    re.Result
    ,t.Release
    ,tc.TestCase
FROM
    Tests t
    LEFT JOIN TestCases tc ON t.TestCaseID = tc.ID
    LEFT JOIN ResultEnums re ON re.ID = t.Result

回复收藏 0 原文

你列表最软的妹 2024-11-07 23:24:40

SELECT Tests.Release, Testcases.TestCase, Tests.Result, ResultEnums.Result
FROM Tests LEFT JOIN Testcases ON Tests.TestCaseID=Testcases.ID
LEFT JOIN ResultEnums ON ResultEnums.ID=Tests.Result;

编辑我没有在 Access 上进行测试，但在 SQL Server 中你可以做类似的事情。

SELECT Tests.Release, Testcases.TestCase, Tests.Result, ResultEnums.Result
FROM Tests LEFT JOIN Testcases ON Tests.TestCaseID=Testcases.ID
LEFT JOIN ResultEnums ON ResultEnums.ID=Tests.Result;

EDIT I didn't test on Access, but in SQL Server you could do something akin to this.

回复收藏 0 原文

~没有更多了~