CODEWITHSUNDEEP
sqloracle-database
Sam
Sam

Reputation: 1229

Trying to retrieve rows where an id does not exist for a user

I am trying to write a query that returns only the users where they do not have a role as 1.

The table columns are roleid, userid, usertype. The userid has an entry for each roleid. So for example: 

roleid     userid
  1           323    
  3           323
  4           323
  6           323
  7           323
  10          323    
  3           324
  4           324
  6           324
  7           324
  10          324

Everytime I try this it just takes out the row where the roleid is 1 but I need a whole user i.e 323 not to show as it has 1.

Would really appreciate help with this.

Thanks

Edit: 

select * from usersroles where userid in (
select userid from uprofiles where networkid in 
(select network_id from network where usertype in (469,467,466,468))) and roleid !=1;

I am using the above but this returns the users without roleid 1 but still returns the users with the other roles. I want the query to only return users that do not have 1.

Upvotes: 1

Views: 537

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

Now that you have edited your request, here is your corrected select statement:

select * from usersroles where userid in 
(
  select userid from uprofiles where networkid in 
  (
    select network_id from network where usertype in (469,467,466,468)
  )
)
and userid not in
(
  select userid from usersroles where roleid =1
);

Or:

select * from usersroles where userid in 
(
  select userid from uprofiles where networkid in 
  (
    select network_id from network where usertype in (469,467,466,468)
  )
  minus
  select userid from usersroles where roleid =1
);

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

You either need the exists clause:

select userid from users 
where not exists (select * from userroles where userid = users.userid and roleid = 1);

Or you subtract the set of users with roleid 1 from the set of all users:

select userid from users
minus 
select userid from userroles where roleid = 1;

Upvotes: 1

Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

Is it so EASY??? 

select distinct userid from table where UserID <> 1

Upvotes: 1

SWeko
SWeko

Reputation: 30902

If you have a users table, than you could do:

select ID -- other relevant user fields here
from users
where ID not in (select userId from userRoles where roleId = 1)

If you do not have a separate table for the users (i.e. the userId field is not a foreign key), than you could do:

select distinct userId 
from userRoles
where userId not in (select userId from userRoles where roleId = 1)

In both cases, the subquery select userId from userRoles where roleId = 1 will give you a list of users that do have the 1 role, and you just need to make sure that ID of the user is not in that list.

Upvotes: 0

Related Questions