CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mani
Mani

Reputation: 741

Fetch uncommon records based on status

I have two tables. I need to fetch the records from T2 which are not present in T1 based on Status. I am not sure if I do left outer join I get all the records from T1 . I understand the reason but not sure how to fix it . Can you please guide .

T1

enter image description here

and T2

enter image description here

Output:

enter image description here

Query I tried.

SELECT T2.*
FROM T2 LEFT OUTER JOIN T1 
ON T2.DOC_NO=T1.DOC_NO
WHERE T2.STATUS='Cleared' and T1.STATUS !='Cleared'

Upvotes: 1

Views: 43

Answers (2)

gogocho
gogocho

Reputation: 218

Based on example you provided it is not clear how Status filtering is used. It looks more like you need MINUS operator:

select doc_no, status, amount from t2
minus
select doc_no, status, amount from t1;

Thanks.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use the NOT EXISTS as follows:

SELECT T2.*
  FROM T2 
 WHERE NOT EXISTS 
  (SELECT 1 FROM T1
    WHERE T1.DOC_NO = T2.DOC_NO
      AND T1.STATUS = 'Cleared');

Upvotes: 1

Related Questions