Postgres return nothing when other rows have value

Question

I'm trying to do a query where in the database there's multiple groups in multiple rows, but if it one of the rows meets a certain criteria I want the query to return nothing.

I tried with CASE but that doesnt seem to work something like this

select *, 
    CASE WHEN groupname = 'is_%' and groupname != 'is_banned' THEN false END 
from usersgroups 
WHERE username = 'tu1';

not sure what to do.

Thanks

EDIT:

This is the way I have the database atm

username|usergroup
tu1     |is_user
tu1     |is_banned
tu2     |is_user

so what I'm looking to get is the query to only return values if a user is part of any is_ groups and they arent is_banned, but problem is that they are on different rows

Thanks again

Answer 1

select *
from usersgroups 
WHERE
    username = 'tu1'
    and exists (
        select 1
        from usersgroups
        where
            username = 'tu1'
            and groupname like 'is_%'
    )
    and not exists (
        select 1
        from usersgroups
        where
            username = 'tu1'
            and groupname = 'is_banned'
    )