CODEWITHSUNDEEP
sqlsql-serverdatabase
Tom Pickles
Tom Pickles

Reputation: 900

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:

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

Upvotes: 1

Views: 64

Answers (4)

mkRabbani
mkRabbani

Reputation: 16908

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

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12959

you can use below query. I have tested this in SQL Server 2014. 

CREATE TABLE #Person(person_id INT)

INSERT INTO #person
values
(10001),
(10002),
(10003),
(10004);

CREATE TABLE #Dates (date_Type VARCHAR(50), [datevalue] datetime)

INSERT INTO #Dates
VALUES
('PUBLIC_HOLIDAY','2020-04-10 00:00:00.000'),
('PUBLIC_HOLIDAY','2020-04-13 00:00:00.000');

CREATE TABLE #Absence (person_id int, datevalue datetime, absence_type VARCHAR(50))

INSERT INTO #Absence
VALUES
(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');

SELECT p.person_id, od.datevalue
FROM #Person AS p
CROSS JOIN (SELECT * FROM #Dates WHERE date_type ='PUBLIC_HOLIDAY') AS od
WHERE NOT EXISTS
(
SELECT 1 FROM 
#Absence AS a
INNER JOIN #Dates AS d
ON a.datevalue = d.datevalue
WHERE a.absence_type = 'Holiday' AND d.date_type = 'PUBLIC_HOLIDAY'
AND a.person_id = p.person_id and d.datevalue = od.datevalue)

Below is the resultset:

+-----------+-------------------------+
| person_id |        datevalue        |
+-----------+-------------------------+
|     10003 | 2020-04-10 00:00:00.000 |
|     10004 | 2020-04-10 00:00:00.000 |
|     10002 | 2020-04-13 00:00:00.000 |
|     10004 | 2020-04-13 00:00:00.000 |
+-----------+-------------------------+

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try:

select distinct person_id from absence a
where absence_type = 'HOLIDAY'
and not exists (select 1 from dates
                where date = a.date 
                and date_type = 'PUBLIC_HOLIDAY')
union all
select person_id from person p
where not exists ( select 1 from absence
                   where p.person_id = person_id)

If you want to have them with dates, use below query:

select person_id, date from absence a
where absence_type = 'HOLIDAY'
and not exists (select 1 from dates
                where date = a.date 
                and date_type = 'PUBLIC_HOLIDAY')
union all
-- in person table we don;t have any dates
select person_id, null from person p
where not exists ( select 1 from absence
                   where p.person_id = person_id)

Upvotes: 0

GMB
GMB

Reputation: 222482

I think that you want a cross join to generate all combinations of persons and dates, and then not exists to filter on those that do not exist in the absence table:

select p.*, d.*
from person p
cross join dates d
where 
    d.date_type = 'PUBLIC_HOLIDAY'
    and not exists (
        select 1 
        from absence a 
        where a.person_id = p.person_id and a.date = d.date and a.absence_type = 'HOLIDAY'
    )

Upvotes: 0

Related Questions