So, this query returns result 1 even if there is no tasks related to list. How to fix it so it returns 0 for lists with zero tasks?
SELECT
l.list_id, l.list_name, l.list_time_created,
COUNT(CASE WHEN t.task_id IS NULL THEN 0 ELSE 1 END) AS numberOfTasks,
SUM(CASE WHEN t.task_completed IS NULL THEN 0 ELSE t.task_completed END) AS numberOfCompletedTasks
FROM list l
LEFT JOIN task t ON l.list_id = t.list_id
WHERE l.user_id = ':user_id'
GROUP BY l.list_id;
If there are no tasks, then the rows will be filtered out. You want a left join:
SELECT l.list_id, l.list_name, l.list_time_created,
COUNT(t.task_id ) AS numberofTasks,
SUM(t.task_completed = 1) AS numberofCompletedTasks
FROM list l LEFT JOIN
task t
ON l.list_id = t.list_id
WHERE l.user_id = ':user_id'
GROUP BY l.list_id;
I introduced table aliases. These make a query easier to write and to read.
I also simplified the logic for numberofCompletedTasks. Your original formulation is fine (and even standard SQL), but MySQL has a nice short-cut.
