CODEWITHSUNDEEP
sqloracle-database
flyingpie
flyingpie

Reputation: 79

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

Upvotes: 1

Views: 736

Answers (5)

S.Bozzoni
S.Bozzoni

Reputation: 1002

Equivalent to the other examples but not using "not exists"

SELECT *
FROM   table1 t1
WHERE  'COMP' <> ALL (SELECT t2.holdstatus
                     FROM   tables t2
                     WHERE  t2.wonum = t1.wonum);

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

A slightly different solution could be with outer join:

select t1.wonum
from table1  t1
     left outer join table2 t2
     on t1.wonum = t2.wonum
        and t2.holdstatus = 'COMP'
where t2.wonum is null

Upvotes: 0

bbrumm
bbrumm

Reputation: 1352

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

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133370

Could be you need a where in 

select * from table1 
where wonum in (select distinct wonum  from table2  holdstatus != 'COMP');

Upvotes: 0

Siyual
Siyual

Reputation: 16917

You are close, you just need to use a NOT EXISTS and reverse your holdstatus condition:

Select  *
From    table1  t1
Where Not Exists
(
    Select  *
    From    table2  t2
    Where   t1.wonum = t2.wonum
    And     t2.holdstatus = 'COMP'
);

Upvotes: 1

Related Questions