CODEWITHSUNDEEP
sqlselect
Echofiend
Echofiend

Reputation: 63

Selecting only one result from a set

I have a user table (id,group) and need to select 1 if a user is not a member of a group. A Member can be a member of multiple groups so there may be more than one record for each ID(not unique)

I can only return one(pass/fail) value and I need to basically check all of the records to see if the user is a member of 'admin'. If the are a member of 'admin' i need the statement to fail. If none of the user's groups is admin then I need it to pass. I have tried a couple of different ways but I dont know if I am on the correct path.

SELECT 1 
FROM GRP
WHERE USER = 'name'
AND GROUP NOT IN ('ADMIN')

SELECT 0 
FROM GRP
WHERE USER = 'name'
AND EXISTS
    (
        SELECT 1 FROM GRP
        WHERE USER = 'name'
        AND GROUP NOT IN ('ADMIN')
    )

EDIT = How I solved my problem

SELECT 1
FROM GRP
WHERE 'name' NOT IN
    (
        select USER
        from GRP
        group by USER
        having sum(case when GROUP = 'ADMIN' then 1 else 0 end) = 1
    )

Upvotes: 0

Views: 102

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use group by and then check whether the admin group is in the list in the having clause:

select user
from grp
group by user
having sum(case when group = 'ADMIN' then 1 else 0 end) = 0;

The above returns all users. You can add the where clause for a particular user if you like.

If you want a flag for the user, instead of filtering out the ones in the admin group:

select user, max(case when group = 'ADMIN' then 1 else 0 end) as AdminFlag
from grp
group by user;

EDIT:

If you want a 0 or 1 flag for a given user:

select max(case when group = 'ADMIN' then 1 else 0 end) as IsInAdminFlag
from grp
where user = 'name';

Upvotes: 1

Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

Another answer you could use that will give you a list of all users and their states. Just add filter by name.

select
  user,
  max(case when group = 'admin' then 1 else 0 end) state
from
  GRP
where
  user = 'my_name'
group by
  user

Upvotes: 0

T McKeown
T McKeown

Reputation: 12847

SELECT 1 AS COL
FROM GRP
WHERE USER = 'name'
AND GROUP NOT IN ('ADMIN')
UNION ALL
SELECT 0 AS COL
FROM GRP
WHERE USER = 'name'
AND EXISTS
(
    SELECT 1 FROM GRP
    WHERE USER = 'name'
    AND GROUP NOT IN ('ADMIN')
)

Upvotes: 1

Related Questions