CODEWITHSUNDEEP
mysql
user2250471
user2250471

Reputation: 1112

Select row unless value is canceled by another row

FROM    TO     TYPE   
----   ----    ----
John   Jane    Like
Jill   Jane    Like
Jane   John    Hide

How would I write a SELECT statement like this:

SELECT * FROM table WHERE to='Jane' UNLESS from='Jane' AND type='hide'

Such that in the table above, it would return John and Jill, minus John since Jane had chosen to hide John, meaning that only Jill would be returned.

Upvotes: 1

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Use not exists:

select t.*
from t
where t.to = 'Jane' and
      not exists (select 1
                  from t t2
                  where t2.from = t.to and t2.type = 'Hide'
                 );

Upvotes: 3

Related Questions