CODEWITHSUNDEEP
sqloracle-database
vignesh waran
vignesh waran

Reputation: 1

Oracle SQL - Need to eliminate data if at least one of the particular condition is not satisfied

My question is related to Oracle sql. I have a two tables say, study table and another one is study part table. Stdyno is the primary key in study table and (stydyno + sqncno) is the primary key in studypart table.

EG: studypart table has data as below.

studyNo sqnc   part   approvalIN 
--------------------------------
123     1       fgh    Y     
123     2       jhf    N
123     3       rty    N
456     1       wer    N
456     2       wdg    N
456     3       ghg    N

I need query in such a way that my output from studypart table gives result as study number which has all the approvalIn as N. If it has at least one of the approvalIn as 'Y' then that studyno should be excluded from the result.

Desired output: studyno: 456

I tried this implementation in stored procedure taking Y and N approvalIn count separately ie, if a studyno has both the count then exclude it and if it has only one count say either N or Y the include it. But i would like to know how to achieve this is query.

Upvotes: 0

Views: 23

Answers (1)

lemon
lemon

Reputation: 15492

You can do it by excluding those rows whose count of "approvalIN = 'N'" does not match the total count of "approvalIN" values.

SELECT STUDYNO
FROM tab
GROUP BY STUDYNO
HAVING SUM(CASE WHEN approvalIN = 'N' THEN 1 END) = COUNT(approvalIN)

Check the demo here.

Upvotes: 1

Related Questions