Return result from query even if WHERE clause not met

Question

I am creating a query that ensures some constraints are met. Here's a semi-working version right now:

SELECT CASE
            WHEN TaskId IS NULL THEN 0
            ELSE 1  
        END AS TaskExists, 
        CASE
            WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
            WHEN IsDownTask = 1 THEN 1
            ELSE 0
        END AS PressReady,
        CASE 
            WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
            ELSE 0
        END AS DownTaskAssignedToDifferentMachine   
FROM Task T
WHERE TaskId = 555555

This works fine when TaskId exists in the Task table, but I also need to return values if that Task doesn't exist (hence the TaskExists field).

For a query on a non-existent Task, I'd expect to return

• TaskExists 0
• PressReady 0
• DownTaskAssignedToDisfferentMachine 0

How can I modify my query to return this even when no TaskId exists?

Answer 1

If you want to return those values just wrap each column with a SUM and an ISNULL:

SELECT ISNULL(SUM(CASE
        WHEN TaskId IS NULL THEN 0
        ELSE 1  
    END), 0) AS TaskExists, 
    ISNULL(SUM(CASE
        WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
        WHEN IsDownTask = 1 THEN 1
        ELSE 0
    END), 0) AS PressReady,
    ISNULL(SUM(CASE 
        WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
        ELSE 0
    END), 0) AS DownTaskAssignedToDifferentMachine    

Answer 2

Please try the below code. I haven't tested.

SELECT CASE
                WHEN TaskId IS NULL THEN 0
                ELSE 1  
            END AS TaskExists, 
            CASE
                WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
                WHEN IsDownTask = 1 THEN 1
                ELSE 0
            END AS PressReady,
            CASE 
                WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
                ELSE 0
            END AS DownTaskAssignedToDifferentMachine   
    FROM Task T
    WHERE  1= case when TaskId = 555555 then 1 
    else 0 end 

Answer 3

You can try something like this:

DECLARE @task INT
SET @task = 555555

SELECT CASE
            WHEN TaskId IS NULL THEN 0
            ELSE 1  
        END AS TaskExists, 
        CASE
            WHEN IsDownTask = 0 AND TaskStatus = 63 THEN 1
            WHEN IsDownTask = 1 THEN 1
            ELSE 0
        END AS PressReady,
        CASE 
            WHEN IsDownTask = 1 AND MachineId <> 2710 THEN 1
            ELSE 0
        END AS DownTaskAssignedToDifferentMachine   
FROM Task T
WHERE TaskId = @task
UNION ALL 
SELECT 0 TaskExists, 0 PressReady, 0 DownTaskAssignedToDifferentMachine   
WHERE NOT EXISTS (SELECT * FROM  Task WHERE TaskId = @task)

Answer 4

If it must be in a query, rather than in the code, then just cheat and tack the default row on to the end of your query. Guarantee that the default sorts after any possible veritable result, and limit to your first row:

SELECT TOP 1 * FROM (
  SELECT 1 AS TaskExists, CASE ... END AS PressReady, CASE ... AS WowYouHaveALongFieldNameHere
    FROM Task
   WHERE TaskID = 55555

  UNION

  -- default if no matching row
  SELECT 0,               0,                          0
) ORDER BY TaskExists DESC;

You may find this more readable than alternatives involving aggregate functions or forcing a join and COALESCE/ISNULLing, etc.