Return row from main table if no results are found in joined tables-CodePudding

I want to write a query which would return all the rows of the main table after a left join on 2 other tables. In this case, I want to return all the job categories and count the number of jobs that have recured for that job category. If there are no jobs that have recurred, then use 0 for that column.

Recurring job table is having a foreign key job_id which links to the job table.

There are currently three job categories and so for instance if there are only 2 jobs that have recurred, then not all the job categories would be shown in the results.

This is my query:

SELECT 
jc.category_name, COUNT( NULLIF(jc.id, 0) ) job_category
FROM `job_category` jc
LEFT OUTER JOIN `job` ON `job`.`job_category_id` = jc.id
LEFT OUTER JOIN `recurring_job` rj ON rj.job_id = `job`.`id` AND rj.id IS NOT NULL
WHERE rj.id IS NOT NULL
GROUP BY jc.id;

Can someone point me in the right direction? Thanks.

CodePudding user response：

You should count the column job_id of the table recurring_job and remove all the conditions rj.id IS NOT NULL:

SELECT jc.id, jc.category_name, 
       COUNT(rj.job_id) counter
FROM job_category jc
LEFT OUTER JOIN job ON job.job_category_id = jc.id
LEFT OUTER JOIN recurring_job rj ON rj.job_id = job.id
GROUP BY jc.id, jc.category_name;

If id is the PRIMARY KEY of job_category (it looks like it is), you can omit category_name from the GROUP BY clause.

Depending on your reqirement, if you want to count the number of distinct recurring jobs you should use:

COUNT(DISTINCT rj.job_id)

CodePudding user response：

Credit to @forpas

There is no need for a WHERE clause in my case.

SELECT jc.category_name, 
            COUNT(rj.job_id) count
            FROM `job_category` jc
            LEFT OUTER JOIN `job` ON `job`.job_category_id = jc.id AND `job`.`date_added` BETWEEN '2021-01-01' AND '2021-01-25'
            LEFT OUTER JOIN `recurring_job` rj ON rj.job_id = `job`.id

            GROUP BY jc.id, jc.category_name

This outputs all the columns and their corresponding count of recurring jobs.

I hope this is useful.