kerv kerv - 25 days ago 8
SQL Question

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?

Answer

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.

Comments