1

I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem is how can i get the users that are not in the role for example is the procurement because in my query I still get the users that are in procurement role? Here is my query.

user to role relationship

Admin role_id = 1

Procurement role_id = 2

Accounting role_id = 3

HR role_id = 4

SELECT 
  `users`.`user_id`,
  `users`.`username`,
  `users`.`email`,
  `users`.`last_login`,
  `users`.`last_ip`,
  `users`.`created_on`,
  `users`.`status` AS user_status,
  `roles`.`role_id`,
  `roles`.`role_name`,
  `roles`.`role_code`,
  `roles`.`status` AS role_status 
FROM
  (`users`) 
  INNER JOIN `userroles` 
    ON `users`.`user_id` = `userroles`.`user_id` 
  INNER JOIN `roles` 
    ON `userroles`.`role_id` = `roles`.`role_id` 
WHERE `roles`.`role_id` != '2' 
ORDER BY `users`.`user_id` ASC 

Let's say that we have user1, user2, and user3.

User = Role

user1 = Admin

user2 = Procurement, Accounting

user3 = Procurement

When I run the query above, it will show the user1 and user2.

1 Answer 1

2
SELECT ...
  FROM users u
  LEFT JOIN userroles ur ON ur.user_id = u.user_id AND ur.role_id = 2
 WHERE ur.role_id IS NULL
 ORDER BY u.user_id ASC;

Examine all rows of user, and their matching row from userroles with a role_is = 2 if it exists, eliminating the rows where it does indeed exist by requiring the role_id in the (non-existent) row to be null... which, since the row does not exist, is indeed null.

Alternately,

SELECT ...
  FROM users u
 WHERE NOT EXISTS (SELECT * FROM userroles ur WHERE ur.user_id = u.user_id AND ur.role_id = 2)
 ORDER BY u.user_id ASC;

Note that the SELECT * in the subquery doesn't actually select everything, it's just a way to express the test of whether such a row exists.

1
  • Wow it works! thanks man but how you come up with that query? Jun 20, 2015 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.