CODEWITHSUNDEEP
sqlms-access
Pablo
Pablo

Reputation: 29529

how to combine left joins?

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

Upvotes: 0

Views: 2898

Answers (4)

taylonr
taylonr

Reputation: 10790

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.

Upvotes: 0

Thomas
Thomas

Reputation: 64674

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.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

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 
;

or

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
;

Upvotes: 1

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56418

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

Upvotes: 0

Related Questions