How to select users that doesn't in the role

Question

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.

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.

Answer 1

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.