mysql - selecting count(tb2.field_name) based on tb1.id of the table1 and tb2.field

Question

this question might have been asked before, but I could not find. because I was not able to ask correctly all situations. so pardon me if it is a repeated question. I have 2 tables: tb1 - categories tb2 - tasks I want to select all tasks and quantity of tasks by each category.

What I did is this:

SELECT category_id, category_title, 
       (SELECT count(task_id) FROM tasks_of_app WHERE category_id ) AS counted_tasks 
FROM categories_of_app

but I get all tasks' quantity for each category.

I can't use WHERE because there is not input param.

please, guys help out or tell what book to read to be able to make such kind of queries.

Answer 1

WHERE category_id needs to specify what category_id should be. You need to match it with the value from the other table to make it a correlated subquery.

SELECT category_id, category_title, 
       (SELECT count(task_id) 
        FROM tasks_of_app 
        WHERE tasks_of_app.category_id = categories_of_app.category_id) AS counted_tasks 
FROM categories_of_app

But a better way to write this is as a LEFT JOIN between the tables.

SELECT c.category_id, c.category_title, COUNT(t.task_id) AS counted_tasks
FROM categories_of_app AS c
LEFT JOIN tasks_of_app AS t ON c.category_id = t.category_id
GROUP BY c.category_id