Mysql – How to select users that doesn’t in the role

MySQLselect

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.

Best Answer

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.

Related Solutions

Thesql inner join producing more results than it should

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;

Mysql – User roles and permissions

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data. You could have very simple code which interprets each permission strictly at face value. To do an administrative task, the user would need an 'A' permission. To do a publishing task, they would need a 'P' permission, etc. In this case an administrator would need three records per site (one each of A, P, E). Similarly publishers would need two records per site and editors only one. There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site. In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'. The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column. If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.

Best Answer

Related Solutions

Thesql inner join producing more results than it should

Mysql – User roles and permissions

Related Question