CODEWITHSUNDEEP
sqlsql-server
Sérgio Rebelo
Sérgio Rebelo

Reputation: 101

How to check in SQL a condition for several rows with same operation? (see details)

I'm having an uncommon situation here.

I have the following data

Column A  Column B
--------  --------
9999      A
9999      A    
9999      V
9999      A
9999      N

7777      A
7777      A    
7777      A
7777      A
7777      N

Expected Result: Only rows with 7777 will appear since there is a row with 9999 with the letter V.

I need to make the following rule. Display Column A if Column B is 'N' and different from V (<> 'V'). I'm having trouble with this since I have several rows with same values in column A and I only can show the value in column A if I have the value 'N' and 'V' must not exist in any row.

I hope I'm explaining this well. Can anyone help?

Thank you a lot!

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use aggregation:

select columnA
from t
group by columnA
having sum(case when columnB = 'N' then 1 else 0 end) > 0 and
       sum(case when columnB = 'V' then 1 else 0 end) = 0 ;

EDIT:

You seem to want:

select t.*
from t
where exists (select 1
              from t t2
              where t2.id = t.id and t2.item = 'N'
             ) and
      not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.item = 'V'
                 );

Upvotes: 2

Dave C
Dave C

Reputation: 7402

Based on the details provided, this is what I believe you want:

SELECT DISTINCT T1.columnA
FROM myTable T1
WHERE T1.columnB='N'
AND NOT EXISTS (SELECT 1 FROM myTable T2 WHERE T2.columnB='V' AND T1.columnA=T2.columnA)

Upvotes: 3

Related Questions