Показать все результаты из одной таблицы, не включенной в другую таблицу (Junction) By: id

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) 

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 ) 

ПРЕДЛАГАЕМЫЙ ЗАПРОС

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; 

ВАШИ ОБРАЗЦЫ ДАННЫХ

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; 

Загружены ваши образцы данных

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> 

ПРЕДЛАГАЕМЫЙ ЗАПРОС ИСПОЛНЕНО

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> 

ДАЙТЕ ПОПРОБУЙТЕ !!!