Cross Apply Including Nulls

Question

I have two tables, Sensors and SensorCalibrations. In the first I hold a list of physical sensors. They need calibrating so in the second table I hold dates (and other information) about the calibration.

So for each row in the Sensor table I may have many rows in SensorCalibrations representing each time the sensor is calibrated.

CREATE TABLE Sensors(
    SensorGUID uniqueidentifier NOT NULL,
    SerialNumber nvarchar(25) NOT NULL,
    ModelName nvarchar(25) NOT NULL,
    SensorAvailable bit NOT NULL,
    SensorType nvarchar(100) NOT NULL,
    DisplayUnits nvarchar(20) NULL,
    LastEdit datetime NOT NULL)

CREATE TABLE SensorCalibrations(
    SensorCalibrationGUID uniqueidentifier NOT NULL,
    SensorGUID uniqueidentifier NOT NULL,
    CalibrationDate datetime NOT NULL,
    Offset float NOT NULL,
    Sensitivity float NOT NULL)

I need a query that will return each Sensor row and the most recent SensorCalibartion for that sensor, linked via the SensorGUID columns in each table. Here is what I have so far using SQL Server 2012:

SELECT s.SerialNumber, c.CalibrationDate
FROM Sensors s
CROSS APPLY (
    SELECT TOP 1 *
    FROM SensorCalibrations c
    WHERE c.SensorGUID = s.SensorGUID
    ORDER BY c.CalibrationDate DESC
) c
ORDER BY c.CalibrationDate DESC

This almost works but no uncalibrated sensors are displayed. Or, that is, no records from the Sensors table are displayed that do not have at least one matching record in the SensorCalibrations table. What I need is for every sensor record to be returned either with its matching most recent calibration date or, if it hasn't been calibrated, just NULL.

Can anyone suggest how I might change my query to achieve this, please?

TIA

Answer 1

SELECT s.SerialNumber, c.CalibrationDate
FROM Sensors s
OUTER APPLY (
    SELECT TOP 1 *
    FROM SensorCalibrations c
    WHERE c.SensorGUID = s.SensorGUID
    ORDER BY c.CalibrationDate DESC
) c
ORDER BY c.CalibrationDate DESC