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.