Find missing rows between three related tables

I have three tables:

Person

person_id
-------------
10001
10002
10003
10004

Dates

date_type      date
-------------- -----------------------
PUBLIC_HOLIDAY 2020-04-10 00:00:00.000
PUBLIC_HOLIDAY 2020-04-13 00:00:00.000

Absence

person_id date                    absence_type
--------- ----------------------- ------------
10001     2020-04-10 00:00:00.000 HOLIDAY
10001     2020-04-13 00:00:00.000 HOLIDAY
10002     2020-04-10 00:00:00.000 HOLIDAY
10003     2020-04-13 00:00:00.000 HOLIDAY

I need to find all of the person_id‘s in the Person table and the date‘s from the Dates table who have not booked any absence matching the following criteria:

• Dates.date_type = ‘PUBLIC_HOLIDAY’
• Absence.absence_type = ‘HOLIDAY’

Basically, I need to find the people and the dates which are public holidays they have not booked an absence for as holiday.

Answer

You can try this below logic-

DEMO HERE

SELECT Person.person_id,Dates.dat,ISNULL(Absence.dat, 'Not Bokked')
FROM Dates
CROSS JOIN Person
LEFT JOIN Absence ON Person.person_id = Absence.person_id AND Dates.dat = Absence.dat 
WHERE Dates.date_type = 'PUBLIC_HOLIDAY'

If you wants only information with not booked, just simply add below line to the script-

AND Absence.dat IS NULL