1

I have 2 tables I'm interacting with.

The first table: acl_permissions (is just a normal table).

+-----------------------------------+
|           acl_permissions         |
+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

The second table: acl_group_permissions is a junction table.

+--------------------------------+
|      acl_group_permissions     |
+----+----------+----------------+
| id | group_id | permissions_id |
+----+----------+----------------+
|  1 |     1    |        1       |
|  2 |     1    |        2       |
|  3 |     1    |        3       |
|  4 |     1    |        4       |
|  5 |     2    |        4       |
|  6 |     2    |        5       |
+----+----------+----------------+

I have a query to show all the permissions that are currently allowed by group_id '1'.

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id = 1

The result is:

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
+----+-------------+----------------+

MY PROBLEM

I want a query to show all the permissions that are not allowed by the group_id '1'.

The desired result would be:

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

The closest I have to it is:

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
LEFT OUTER JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id IS NULL

With the result being:

+----+-------------+----------------+
| id |     name    |   permission   |
+----+-------------+----------------+
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+

How do I feed the group_id of '1' into the query to eliminate the id 4.

I have Googled plenty but I'm find examples and solutions that are close but to exact to my situation.

IE: Using a junction table. My MySQL Join Types infographics are of no help either.

Help..! My brain is melting..!

0

Don’t use a join logic, use instead a simpler one :

Select all permissions_id that group_1 has (from acl_group_permissions), then select all acl_permissions id that are not in the ones selected. Translated to SQL, it becomes:

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
WHERE acl_permissions.id NOT 
IN (
    SELECT permissions_id
    FROM acl_group_permissions
    WHERE group_id = 1
)
| improve this answer | |
  • Thank you. Understandable, logical and in hindsight obvious. Now I know all about NOT IN. – Midnight-Coding Apr 20 '15 at 13:26
0

Not the best, but this would work

SELECT acl_permissions.id, acl_permissions.name, acl_permissions.permission
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id != 1 and acl_permissions.id 
NOT IN (SELECT DISTINCT acl_permissions.id
FROM acl_permissions
JOIN acl_group_permissions
ON acl_permissions.id = acl_group_permissions.permission_id
WHERE acl_group_permissions.group_id = 1)
| improve this answer | |
0

PROPOSED QUERY

SELECT A.*
FROM acl_permissions A LEFT JOIN
(SELECT * FROM acl_group_permissions WHERE group_id=1) B
ON A.id = B.permissions_id WHERE B.id IS NULL;

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS matt;
CREATE DATABASE matt;
USE matt
CREATE TABLE acl_permissions
(
    id int not null auto_increment,
    name varchar(32),
    permission varchar(32),
    primary key (id)
);
CREATE TABLE acl_group_permissions
(
    id int not null auto_increment,
    group_id int not null,
    permissions_id int not null,
    primary key (id)
);
INSERT INTO acl_permissions
(name,permission) VALUES
('Add User'   ,'addUser'),
('Edit User'  ,'editUser'),
('Delete User','deleteUser'),
('View User'  ,'viewUser'),
('Test Name'  ,'testPermission');
INSERT INTO acl_group_permissions
(group_id,permissions_id) VALUES
(1,1),(1,2),(1,3),(1,4),(2,4),(2,5);
SELECT * FROM acl_permissions;
SELECT * FROM acl_group_permissions;

YOUR SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS matt;
Query OK, 2 rows affected (0.39 sec)

mysql> CREATE DATABASE matt;
Query OK, 1 row affected (0.00 sec)

mysql> USE matt
Database changed
mysql> CREATE TABLE acl_permissions
    -> (
    ->     id int not null auto_increment,
    ->     name varchar(32),
    ->     permission varchar(32),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TABLE acl_group_permissions
    -> (
    ->     id int not null auto_increment,
    ->     group_id int not null,
    ->     permissions_id int not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO acl_permissions
    -> (name,permission) VALUES
    -> ('Add User'   ,'addUser'),
    -> ('Edit User'  ,'editUser'),
    -> ('Delete User','deleteUser'),
    -> ('View User'  ,'viewUser'),
    -> ('Test Name'  ,'testPermission');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO acl_group_permissions
    -> (group_id,permissions_id) VALUES
    -> (1,1),(1,2),(1,3),(1,4),(2,4),(2,5);
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM acl_permissions;
+----+-------------+----------------+
| id | name        | permission     |
+----+-------------+----------------+
|  1 | Add User    | addUser        |
|  2 | Edit User   | editUser       |
|  3 | Delete User | deleteUser     |
|  4 | View User   | viewUser       |
|  5 | Test Name   | testPermission |
+----+-------------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM acl_group_permissions;
+----+----------+----------------+
| id | group_id | permissions_id |
+----+----------+----------------+
|  1 |        1 |              1 |
|  2 |        1 |              2 |
|  3 |        1 |              3 |
|  4 |        1 |              4 |
|  5 |        2 |              4 |
|  6 |        2 |              5 |
+----+----------+----------------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT A.*
    -> FROM acl_permissions A LEFT JOIN
    -> (SELECT * FROM acl_group_permissions WHERE group_id=1) B
    -> ON A.id = B.permissions_id WHERE B.id IS NULL;
+----+-----------+----------------+
| id | name      | permission     |
+----+-----------+----------------+
|  5 | Test Name | testPermission |
+----+-----------+----------------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

| improve this answer | |
  • Wow..! I love your systematic approach in answering my question and your answer does work..! The only reason I did not accept this as the answer is because of it's complexity compared to the other. I have never seen a query written like this. I do have a lot more to learn. +1 for your logic and presentation. – Midnight-Coding Apr 20 '15 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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