CODEWITHSUNDEEP
sqldb2
Salva
Salva

Reputation: 113

How to check records not in two tables for a date in another third table using DB2 sql?

I have an Employee table having list of employee names with IDs, from this they flow to two tables with below structure :

EMP Table

ID EMP_NAME
100 BOB

create table EMP (ID varchar(20),EMP_NAME varchar(20)); insert into EMP values('100','BOB')

Table 1

ID NAME DATE
100 BOB 01-10-2021
100 BOB 01-11-2021

create table Table_1(ID varchar(20),NAME varchar(20), DATE date);

insert into Table_1 values('100','BOB','01-10-2021');insert into Table_1 values('100','BOB','01-11-2021');

Table 2

ID NAME DATE
100 BOB 01-11-2021
100 BOB 01-12-2021

create table Table_2(ID varchar(20),NAME varchar(20), DATE date);

insert into Table_2 values('100','BOB','01-11-2021');insert into Table_1 values('100','BOB','01-12-2021');

Table Date

Month DATE
Sep 01-09-2021
Oct 01-10-2021
Nov 01-11-2021
Dec 01-12-2021

create table DATE(Month varchar(20), DATE date); insert into DATE values('Sep','01-09-2021');insert into DATE values('Sep','01-10-2021');insert into DATE values('Sep','01-11-2021'); insert into DATE values('Sep','01-12-2021'))

I want to refer table table 3 (Date Table) to identify on which date of TABLE 3 record did not appear in TABLE 1 and 2 ( as in given case record having date = 01-09-2021 is the expected output)

Expected Output

ID NAME DATE
100 BOB 01-09-2021

Upvotes: 1

Views: 208

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I want to refer table table 3 (Date Table) to identify on which date of TABLE 3 record did not appear in TABLE 1 and 2

If you just want the overall dates that don't appear, you can use not exists or left join:

select d.*
from dates d
where not exists (select 1 from table1 t1 where t1.date = d.date) and
      not exists (select 1 from table2 t2 where t2.date = d.date);

This is the question that you are asking. However, your desired results suggest that you want the employee/date combinations that do not appear. If that is the case, then CROSS JOIN the emp and dates tables and filter out the ones that don't exist. One approach:

select e.*, d.*
from emp e cross join
     dates d left join
     table1 t1
     on t1.date = d.date and t1.id = e.id left join
     table2 t2
     on t2.date = d.date and t2.id = e.id
where t1.id is null and t2.id is null;

Upvotes: 1

Mark Barinstein
Mark Barinstein

Reputation: 12339

Try this:

SELECT E.ID, E.EMP_NAME, D.DATE
FROM DATE D, EMP E
WHERE 
    NOT EXISTS (SELECT 1 FROM Table_1 T WHERE T.ID = E.ID AND T.DATE = D.DATE) 
AND NOT EXISTS (SELECT 1 FROM Table_2 T WHERE T.ID = E.ID AND T.DATE = D.DATE)

Upvotes: 0

Related Questions