Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table taba (id int);
create table tabb (id int, value varchar(100), lastupdated date);
create table tabc (id int, value varchar(100), lastupdated date);
insert into taba values (100);
1 rows affected

insert into taba values (101);
1 rows affected

insert into taba values (102);
1 rows affected

insert into tabb values (99,'Y',trunc(sysdate)-3);
1 rows affected

insert into tabb values (100,'N',trunc(sysdate)-2);
1 rows affected

insert into tabb values (103,'N',trunc(sysdate)-1);
1 rows affected

insert into tabc values (102,'Y',trunc(sysdate)-4);
1 rows affected

insert into tabc values (103,'Y',trunc(sysdate));
1 rows affected

insert into tabc values (104,'N',trunc(sysdate)-3);
1 rows affected

SELECT id, value FROM
(SELECT id, value, ROW_NUMBER()
OVER (PARTITION BY id ORDER BY lastupdated DESC) AS rowNr
FROM
(SELECT id, 'N' AS value, sysdate AS lastupdated FROM taba
WHERE id NOT IN (SELECT id FROM tabb) 
AND id NOT IN (SELECT id FROM tabc)
UNION
SELECT id, value, MAX(lastupdated) AS lastupdated
FROM tabb
GROUP BY id, value
UNION
SELECT id, value, MAX(lastupdated) AS lastupdated
FROM tabc
GROUP BY id, value
ORDER BY id, lastupdated DESC)) WHERE rownr = 1;


ID
VALUE


99
Y

100
N

101
N

102
Y

103
Y

104
N