By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableb (tablea_id, status) as
select 1000, 'RE' from dual
union all
select 1000, 'RE' from dual
union all
select 1000, 'RE' from dual
union all
select 1000, 'CO' from dual
union all
select 2000, 'RE' from dual
union all
select 2000, 'RE' from dual
6 rows affected
SELECT B.TableA_ID,B.status
FROM TableB B
WHERE NOT EXISTS (
SELECT null
FROM TableB B2
WHERE B2.TableA_ID=B.TableA_ID
AND B2.status in ('CO','CL')
);
| TABLEA_ID | STATUS |
|---|---|
| 2000 | RE |
| 2000 | RE |
SELECT TableA_ID, status
FROM (
SELECT B.TableA_ID,B.status,
COUNT(case when B.status in ('CO','CL') then 1 end)
OVER (partition by B.TableA_ID) AS cnt
FROM TableB B
)
WHERE cnt = 0;
| TABLEA_ID | STATUS |
|---|---|
| 2000 | RE |
| 2000 | RE |