How to get count of registered sets combination and non registered sets as '0' value-CodePudding

I had 2 individual tables named manager, employee. In the manager table we have ID, a list of managers, in the employee table we have ID, a list of employees, and another column named manager_id as a foreign key. Total 20 combinations Now there is another table called registration which has manager and employee as separate columns along with other columns. I want a select query of All 20 manager and employee combinations for which non registered combinations should appear as '0'. I tried with below query but not getting expected output

Query:

select r.manager,r.employee,count(*) 
from registration r 
group by r.manager,r.employee
union all 
select s.manager, i.employee,null
from manager m, employee e
where m.id = e.manager_id

---> It is showing output in a way where the registration table list is coming first with count and later with all 20 combinations as null value in count column

CodePudding user response：

I don't know the exact column names of the registration table, but the query should be something like this:

select
    e.id,
    e.manager_id,
    (case r.employee_id is null then 0 else 1 end) as is_registered
from
    employee e
    left join registration r
        on r.employee_id = e_id and r.manager_id = e.manager_id

CodePudding user response：

This is the query I modified

 select m.manager, e.employee,
 sum(case when r.employee is null then 0 else 1 end) as total
 from employee e
   left join registration r on r.employee = e.employee
   left join manager m on m.id = e.manager_id 
   group by m.manager, e.employee, r.employee