CODEWITHSUNDEEP
sql
Gordon Cai
Gordon Cai

Reputation: 55

SQL: select using two conditions on one columns

I need to extract distinct elements of a column by using two conditions on another column. For example: 

c1  |  c2  
---------
u   |  a
v   |  b
w   |  c
x   |  a
x   |  b
x   |  c
y   |  a
y   |  c

I want the entries of c1 when c2 does contain 'a' but not 'b'. The returned names should be 'u' and 'y'.

Any help are appreciated, thanks.

Upvotes: 0

Views: 60

Answers (3)

Moher
Moher

Reputation: 741

SELECT DISTINCT t.c1
FROM table as t
WHERE 
t.c2 LIKE '%a%'
AND NOT t.c2 LIKE '%b%'

Upvotes: 0

pojo-guy
pojo-guy

Reputation: 969

This isn't something the SQL excels at, so it's a bit clunky. This naive implementation is fairly vanilla and should work with little modification under most dialects.

In DB2 you may need to add "with ur" to the inner query to prevent locking. 

select c1 from yourtable
where c2 = 'a'
  and c1 not in (
    select c1 from yourtable where c2 = 'b'
  )

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Perhaps the conceptually easiest ways to do this would be to aggregate on the c1 column and just assert that a is present and b is not present:

SELECT c1
FROM yourTable
GROUP BY c1
HAVING
    SUM(CASE WHEN c2 = 'a' THEN 1 ELSE 0 END) > 0 AND
    SUM(CASE WHEN c2 = 'b' THEN 1 ELSE 0 END) = 0

Upvotes: 3

Related Questions