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