CODEWITHSUNDEEP
sql
Vivian River
Vivian River

Reputation: 32430

Why does my SQL query return rows with NULL? It should never return rows with NULL

Suppose I have a SQL table called AT_Devices with each record representing a piece of hardware. I have a second table called AT_Event_History which describes "events" that occur for pieces of hardware. An event can consist of something like the piece of equipment being lost, destroyed, retired, etc. Each event has a corresponding status code.

I've written some SQL to return the code of the most recent event for each record in AT_Devices. I have a business rule that if the device has no events on record, the status code should be 0.

I've written my SQL query to return 0 in this case, but for some reason, it is returning NULL. Why?

SELECT atDeviceHistory.StatusCodeNotNull AS StatusCode0  
 FROM dbo.AT_Devices atd LEFT OUTER JOIN 
(
    SELECT DeviceID,
     ParentCode,
     (CASE WHEN (StatusCode IS NOT NULL) THEN StatusCode ELSE 0 END) AS  
     StatusCodeNotNull,  
     WhenEntered AS StatusDate
FROM AT_Event_History as A
WHERE A.ParentCode=0 
AND A.WhenEntered >= (SELECT MAX(WhenEntered) 
                      FROM AT_Event_History AS B 
                      WHERE A.DeviceID=B.DeviceID AND ParentCode=0)
 ) atDeviceHistory ON atd.DeviceID=atDeviceHistory.DeviceID

Upvotes: 1

Views: 242

Answers (2)

Andriy M
Andriy M

Reputation: 77737

Now that @Abe Miessler has pointed out the misplaced NULL check in your query, you could actually get rid of the subselect and rewrite the entire query like this:

SELECT
  COALESCE(h.StatusCode, 0) AS StatusCode0,
  … /* whatever other columns you might need */
FROM dbo.AT_Devices atd
  LEFT OUTER JOIN AT_Event_History h ON atd.DeviceID = h.DeviceID
    AND h.ParentCode = 0
    AND h.WhenEntered = (SELECT MAX(WhenEntered)
                         FROM AT_Event_history
                         WHERE DeviceID = a.DeviceID AND ParentCode = 0)

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85126

Since you are doing a left join, if there are no matching records a null value will be returned for anything referencing the atDeviceHistory columns. If you do not want to return records with no match, then change this to an inner join. I would also recommend changing your case statement to a COALESCE:

SELECT DeviceID,
     ParentCode,
     COALESCE(StatusCode ,0) AS StatusCodeNotNull,   
     WhenEntered AS StatusDate
FROM AT_Event_History as A

UPDATE:

Try this:

SELECT COALESCE(atDeviceHistory.StatusCode,0)  AS StatusCode0  
 FROM dbo.AT_Devices atd LEFT OUTER JOIN 
(
    SELECT DeviceID,
     ParentCode,
     StatusCode  
     WhenEntered AS StatusDate
FROM AT_Event_History as A
WHERE A.ParentCode=0 
AND A.WhenEntered >= (SELECT MAX(WhenEntered) 
                      FROM AT_Event_History AS B 
                      WHERE A.DeviceID=B.DeviceID AND ParentCode=0)
 ) atDeviceHistory ON atd.DeviceID=atDeviceHistory.DeviceID

Upvotes: 4

Related Questions