Oracle Loop through values from one table and join with another table I have a table (table1) with fields package_id and contract_id.

Data in table 1 is like that 1 package_id can have 1 or more contract_id associated with it.

I have another table (table2) with contract_id and status. status can be ('active' or 'dead')

I need to find the package_id which doesn't have any contract_id associated with it which is 'ACTIVE' or in other words I need to find package_id which has all contract_id associated with it in 'DEAD' state.

Please help.
Two ways to do this would be with NOT EXISTS with an INNER JOIN or MINUS

INNER JOIN

SELECT * 
FROM table1 t1
WHERE NOT EXISTS
(
SELECT t2.contract_id
FROM table2 t2
INNER JOIN table1 t3
ON t3.contract_id = t2.contract_id
WHERE t2.status = 'ACTIVE' 
AND t3.package_id = t1.package_id
);


MINUS

SELECT * 
FROM table1 t1
WHERE EXISTS
(
SELECT t2.contract_id
FROM table2 t2
WHERE t2.status = 'ACTIVE' 
MINUS
SELECT t3.contract_id 
FROM table1 t3
WHERE t3.package_id = t1.package_id
);


DB &lt;> Fiddle