CODEWITHSUNDEEP
sql
kerv
kerv

Reputation: 315

How to exclude a record from sql with specific value

I have a question about my sql query. I need to exclude all the rows that have the value 'f' in it.

I have tried doing

SELECT * FROM table WHERE type NOT IN ('f')

But this doesnt seem to be working. Any help would be appreciated

EDIT: sorry for being unclear,

The problem I have is that theres multiple rows like this

name   type

test1  f
test1  l

If i would use this query it would still return me test1 but what I want is that it returns NULL. Can this be done?

Upvotes: 0

Views: 76

Answers (2)

user330315
user330315

Reputation:

You apparently want to see rows where no other rows for the same name has the value f

select t1.*
from the_table t1
where not exists (select *
                  from the_table t2
                  where t1.name = t2.name 
                    and t2.type = 'f');

If no such row exists the query will not return null it will simply return no rows at all.

Upvotes: 3

sagi
sagi

Reputation: 40481

Use the HAVING clause :

SELECT s.name 
FROM YourTable s
GROUP BY s.name
HAVING COUNT(CASE WHEN s.type = 'f' THEN 1 END) = 0

Upvotes: 0

Related Questions