flyingpie flyingpie - 6 months ago 8
SQL Question

Oracle SQL - Finding records from table1 where table2 condition does not exist

I have 2 tables. Table1 would be the main table.
Table2 contains data related to table1.

Table1:

WONUM
123
124
125


Table2:

wonum task holdstatus
123 1 APPR
123 2 APPR
123 3 APPR

124 1 COMP
124 2 APPR

125 1 COMP
125 2 COMP


I want to select ALL wonum from table1 where table1.wonum = table2.wonum and there are NO records with a table2.HOLDSTATUS = 'COMP'

Any help would be great.

The closet I got was:

select * from table1 where
exists (select 1 from table2 where table1.wonum=table2.wonum and holdstatus != 'COMP');

Answer

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'
);
Comments