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

Clodoaldo Neto · Accepted Answer · 2012-10-24 16:08:52Z

2

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'
    )

edited Oct 24, 2012 at 16:08

answered Oct 24, 2012 at 15:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ShinySides Over a year ago

That still returns all values, I explained myself badly, I edited the original post with a better explanation, thanks

Collectives™ on Stack Overflow

Postgres return nothing when other rows have value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related