Oracle Loop through values from one table and join with another table
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