CODEWITHSUNDEEP
sqlsql-serversql-server-2008
intrigued_66
intrigued_66

Reputation: 17248

What kind of JOIN do I need to perform this type of query?

Assume I have three tables:

In the safety tests table it contains results for each safety test performed on 10 cars from the Cars table. There are a maximum of 20 safety tests- which are obviously obtained from a select statement on the SafetyTests table. However, some of the 10 cars have had all 20 tests performed whilst others have only had 5.

I want to produce a 10 x 20 matrix which will show 20 results per car (even if they havent had 20 safety test results). If the test hasn't been performed on the car yet, it would just show the test name but a value of zero (or null).

I thought this would be a SELECT on the SafetyTests table (to get the distinct list of test IDs) and a LEFT JOIN onto the combination of a JOIN between Cars and TestResults, but the problem is this returns CAR ID of NULL for missing tests because there was no match to the Car table.

Upvotes: 1

Views: 87

Answers (2)

Charleh
Charleh

Reputation: 14012

You can cross join to safety tests from cars - then left join to TestResults - the cross join ensures you get the 10 x 20 matrix, the left join gives you the results

Use ISNULL or COALESCE to replace nulls in the result set with zeroes

e.g.

SELECT car.name, testresults.testname, isnull(testresults.result, 0) FROM 
cars CROSS JOIN
safetytests
LEFT JOIN testresults on safetytests.testid = testresults.testid AND car.id = testresults.carid

Here's the cross join working

Did a sqlfiddle as someone suggested :) real good that site

http://sqlfiddle.com/#!3/2bc73/2

Upvotes: 4

GarethD
GarethD

Reputation: 69759

If I understand the question correctly you want a 10x20 result set something like this:

CarID   |   Test1   |   Test2   |   ....    |   Test20
-------------------------------------------------------
1       |   NULL    |   Fail    |   ....    |   true
2       |   2       |   Pass    |   ....    |   false

To do this I would take advantage of SQL-Server 2008's PIVOT function.

WITH Results AS
(   SELECT  cars.CarID,
            TestName,
            TestValue
    FROM    Cars
            CROSS JOIN SafetyTests s
            LEFT JOIN TestResults res
                ON res.CarID = Cars.CarID
                AND res.TestID = s.TestID
) 
SELECT  *
FROM    Results
        PIVOT
        (   MAX(TestValue)
            FOR TestName IN ([TesT1], [Test2], [Test3], [Test4]) 
            -- LIST ALL 20 TEST NAMES HERE
        ) pvt

The downside of this is that you have to explicity list all the test names to be pivoted otherwise they will not appear as columns, however it is possible to do this dynamically. Below is essentially exactly the same query as above, but I have generated a list of all column names dynamically and inserted them into the query.

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT  @SQL = @SQL + ',' + QUOTENAME(TestName)
FROM    SafetyTests

SET @SQL = 'WITH Results AS
            (   SELECT  Cars.CarID,
                        TestName,
                        TestValue
                FROM    Cars
                        CROSS JOIN SafetyTests s
                        LEFT JOIN TestResults res
                            ON res.CarID = Cars.CarID
                            AND res.TestID = s.TestID
            ) 
            SELECT  *
            FROM    Results
                    PIVOT
                    (   MAX(TestValue)
                        FOR TestName IN (' + STUFF(@SQL, 1, 1, '') + ')
                    ) pvt'

EXECUTE SP_EXECUTESQL @SQL

SQL Fiddle

Upvotes: 3

Related Questions