I have 2 tables. Table1 would be the main table.
Table2 contains data related to table1.
wonum task holdstatus
123 1 APPR
123 2 APPR
123 3 APPR
124 1 COMP
124 2 APPR
125 1 COMP
125 2 COMP
select * from table1 where
exists (select 1 from table2 where table1.wonum=table2.wonum and holdstatus != 'COMP');
You've almost got the right answer. Try this query:
SELECT t1.wonum FROM table1 t1 WHERE t1.wonum NOT IN ( SELECT t2.wonum FROM table2 t2 WHERE t2.wonum = t1.wonum AND t2.holdstatus = 'COMP' );
This should give you all of the records you need. In this case, just record 123.
You can also do it using a NOT EXISTS query. Generally, they perform better, but if you have a small table, then it wouldn't make that much of a difference.
SELECT t1.wonum FROM table1 t1 WHERE NOT EXISTS ( SELECT t2.wonum FROM table2 t2 WHERE t2.wonum = t1.wonum AND t2.holdstatus = 'COMP' );