How to find days when workers don't show up for work-CodePudding

I have a calender table for everyday in 2021. Also i have an entry table for workers everyday entries. It includes id and date columns. Calender just have date column. How can i find the days when the workers did not come? I thought searching days for every id would work

CodePudding user response：

Left join the worker entries to all possible combinations of date & id.
Then the unmatched entries will be seen as NULL.

select cal.cal_date, wrkr.worker_id
from (select distinct workingday as cal_date from worker) cal
cross join (select distinct id as worker_id from worker) wrkr
left join worker as ent
  on ent.workingday = cal.cal_date
 and ent.id = wrkr.worker_id
where ent.id is null
group by cal.cal_date, wrkr.worker_id