Returning a number when result set is null

Each lot object contains a corresponding list of work orders. These work orders have tasks assigned to them which are structured by the task set on the lots parent (the phase). I am trying to get the LOT_ID back and a count of TASK_ID where the TASK_ID is found to exist for the where condition.

The problem is if the TASK_ID is not found, the result set is null and the LOT_ID is not returned at all.

I have uploaded a single row for LOT, PHASE, and WORK_ORDER to the following SQLFiddle. I would have added more data but there is a fun limiter .. err I mean character limiter to the editor.

SQLFiddle

 SELECT W.[LOT_ID], COUNT(*) AS NUMBER_TASKS_FOUND
 FROM [PHASE] P
 JOIN [LOT] L ON L.[PHASE_ID] = P.[PHASE_ID]
 JOIN [WORK_ORDER] W ON W.[LOT_ID] = L.[LOT_ID]
 WHERE P.[TASK_SET_ID] = 1 AND W.[TASK_ID] = 41
 GROUP BY W.[LOT_ID] 

The query returns the expected result when the task id is found (46) but no result when the task id is not found (say 41). I’d expect in that case to see something like:

+--------+--------------------+
| LOT_ID | NUMBER_TASKS_FOUND |
+--------+--------------------+
|    500 |                  0 |
|    506 |                  0 |
+--------+--------------------+

I have a feeling this needs to be wrapped in a sub-query and then joined but I am uncertain what the syntax would be here.

My true objective is to be able to pass a list of TASK_ID and get back any LOT_ID that doesn’t match, but for now I am just doing a query per task until I can figure that out.

Answer

You want to see all lots with their counts for the task. So either outer join the tasks or cross apply their count or use a subquery in the select clause.

select l.lot_id, count(wo.work_order_id) as number_tasks_found
from lot l
left join work_order wo on wo.lot_id = l.lot_id and wo.task_id = 41
where l.phase_id in (select p.phase_id from phase p where p.task_set_id = 1)
group by l.lot_id
order by l.lot_id;

or

select l.lot_id, w.number_tasks_found
from lot l
cross apply
(
  select count(*) as number_tasks_found
  from work_order wo
  where wo.lot_id = l.lot_id
  and wo.task_id = 41
) w
where l.phase_id in (select p.phase_id from phase p where p.task_set_id = 1)
order by l.lot_id;

or

select l.lot_id,
  (
    select count(*)
    from work_order wo 
    where wo.lot_id = l.lot_id 
    and wo.task_id = 41
  ) as number_tasks_found
from lot l
where l.phase_id in (select p.phase_id from phase p where p.task_set_id = 1)
order by l.lot_id;

Another option would be to outer join the count and use COALESCE to turn null into zero in your result.