CODEWITHSUNDEEP
sql
Wajsbrot
Wajsbrot

Reputation: 341

SQL : Conditional query on several rows

I have a table like 

letter | number
a      | 1
a      | 1
b      | 2
c      | 1
c      | 2
c      | 2

and I would like to write a SQL query that only returns rows corresponding to letter values that are associated with both a number '1' and a number '2', i.e. I want to keep only

c | 1
c | 2
c | 2

from my example above.

Can anyone help? Many thanks!

Upvotes: 0

Views: 194

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

You need to use Group By and Having clause

This will give you the letters that are associated to number 1 and 2

SELECT * 
FROM   yourtable 
WHERE  letter IN (SELECT letter 
                  FROM   yourtable 
                  WHERE  number IN ( 1, 2 ) 
                  GROUP  BY letter 
                  HAVING Count(DISTINCT number) = 2)

If you want to find the letters that are associated only to 1 and 2 then use this 

SELECT letter 
FROM   test 
GROUP  BY letter 
HAVING Count(DISTINCT CASE WHEN number = 1 THEN 1 END) = 1 
       AND Count(DISTINCT CASE WHEN number = 2 THEN 1 END) = 1 
       AND Count(DISTINCT number) = 2

Upvotes: 1

Related Questions