By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table EMP(eID number(10), effective_date date);
create table type(tID number(10),contract_type varchar2(2));
insert into emp values (1,to_date('02/14/2023','MM-DD-YYYY'));
1 rows affected
insert into emp values (2,to_date('02/15/2023','MM-DD-YYYY'));
1 rows affected
insert into emp values (3,to_date('04/30/2023','MM-DD-YYYY'));
1 rows affected
insert into emp values (4,to_date('03/24/2023','MM-DD-YYYY'));
1 rows affected
insert into type values (1,'P');
1 rows affected
insert into type values (1,'S');
1 rows affected
insert into type values (1,'P');
1 rows affected
insert into type values (2,'S');
1 rows affected
insert into type values (2,'S');
1 rows affected
insert into type values (3,'P');
1 rows affected
insert into type values (3,'S');
1 rows affected
insert into type values (4,'S');
1 rows affected
SELECT e.eid
FROM emp e
JOIN type t ON e.eid = t.tid
WHERE
e.effective_date >= sysdate
GROUP BY e.eid
HAVING COUNT(CASE WHEN t.contract_type <> 'S' THEN 1 END) = 0;
| EID |
|---|
| 2 |
| 4 |