Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE RELATION (relID INTEGER, passport VARCHAR(7), "date" DATE);


INSERT INTO RELATION (relID, passport, "date") VALUES  ('1', '400000V', to_date('2019-07-21', 'yyyy-mm-dd'));
1 rows affected

INSERT INTO RELATION (relID, passport, "date") VALUES  ('2', '400000V', to_date('2019-02-28', 'yyyy-mm-dd'));
1 rows affected

INSERT INTO RELATION (relID, passport, "date") VALUES  ('3', '400000V', to_date('2011-07-31', 'yyyy-mm-dd'));
1 rows affected

INSERT INTO RELATION (relID, passport, "date") VALUES  ('4', '342342X', to_date('2012-12-31', 'yyyy-mm-dd'));
1 rows affected

INSERT INTO RELATION (relID, passport, "date") VALUES  ('5', '342342X', to_date('2011-10-11', 'yyyy-mm-dd'));
1 rows affected

INSERT INTO RELATION (relID, passport, "date") VALUES  ('6', '823972O', to_date('2019-07-31', 'yyyy-mm-dd'));
1 rows affected

CREATE TABLE PERSON (id INTEGER, passport VARCHAR(8));
INSERT INTO PERSON (id, passport) VALUES ('1', '342342X');
1 rows affected

INSERT INTO PERSON (id, passport) VALUES  ('2', '3333333T');
1 rows affected

INSERT INTO PERSON (id, passport) VALUES  ('3', '1111111W');
1 rows affected

select r.relid, r.passport
from RELATION r
where 
  not exists (
    select 1 from PERSON 
    where passport = r.passport
  ) and
  not exists (
    select 1 from RELATION 
    where passport = r.passport and "date" > r."date"
  )


RELID
PASSPORT


1
400000V

6
823972O