CODEWITHSUNDEEP
phpmysqljoin
kilrizzy
kilrizzy

Reputation: 2943

Mysql Join, check if user has / has no permission

Trying to do this with a single query, but want to get a list of all users who have permission to do x. This involves the users table and permissions table. But it looks like any joins I use doesnt care about the where statement (since the user will usually have another permission in the table)

SELECT DISTINCT `u`.*
FROM (`system_users` AS u)
JOIN `system_user_permissions` AS p ON `u`.`id` = `p`.`user`
WHERE `p`.`permission` != 2
AND `p`.`permission` != 3

I have about 3900 users, I should receive about 2000 users who are not tied to either permission id 2 or 3. But still get 3900 The problem is because the user may also be tied to permission 1,5,6... they still get put in the list after it groups them together.

Any suggestions?

Updated query, still no luck though. If I output "permission" for each one I don't get 2 or 3, the users listed still may have that permission there

SELECT DISTINCT `u`.*, `p`.`permission`
FROM (`system_users` AS u)
INNER JOIN `system_user_permissions` AS p ON `u`.`id` = `p`.`user`
WHERE `p`.`permission` NOT IN (2, 3)

This did the trick:

SELECT * FROM system_users AS u 
        WHERE NOT EXISTS(
            SELECT 1 FROM system_user_permissions AS p
            WHERE u.id = p.user
            AND p.permission IN (2,3)
)

Upvotes: 1

Views: 396

Answers (4)

Eric
Eric

Reputation: 95113

You want not exists:

select
   *
from
   system_users u
where
   not exists (
       select 
           1 
       from 
           system_user_permissions p 
       where 
           p.id = u.user 
           and p.permission in (2,3)
   )

All your original query is doing is negating any user that has only permission 2 or 3, not 1, 2, and 5 (which is what you want). A not exists will throw that user out as soon as a permission 2 or 3 is found in system_user_permissions for that user.

Upvotes: 3

Ricardo Cárdenes
Ricardo Cárdenes

Reputation: 9172

You could approach the opposite way, specifiying which users shouldn't appear in the list, like this:

SELECT DISTINCT `u`.*
FROM (`system_users` AS u)
JOIN `system_user_permissions` AS p ON `u`.`id` = `p`.`user`
WHERE `p`.`user` NOT IN
  (SELECT DISTINCT `user`
   FROM `system_user_permissions`
   WHERE `permission` = 2 OR `permission` = 3);

Upvotes: 1

AlfredoVR
AlfredoVR

Reputation: 4287

Have you tried this? I think your problems is ()

SELECT DISTINCT u.* FROM (system_users AS u) JOIN system_user_permissions AS p ON u.id = p.user WHERE (p.permission != 2 AND p.permission != 3 )

Upvotes: -1

Matt Moore
Matt Moore

Reputation: 581

Use the NOT IN keywords to select a group of values you do not want returned. Obviously, removing the "NOT" would have the opposite affect.

SELECT DISTINCT `u`.* 
FROM (`system_users` AS u) 
INNER JOIN `system_user_permissions` AS p 
   ON `u`.`id` = `p`.`user` 
WHERE `p`.`permission` not in (2,3)

Upvotes: 0

Related Questions