Mostrar en un select solo roles que no sean asignados

[danj]

Saludos a todos

sucede que tengo una consulta la que consiste en mostrarme por medio de un select
el campo nombre de la tabla roles y a su vez que no se vean los roles ya asiganados al usuario que estan almacenados

ejemplo:

usuario= juan david

roles = admin
superAdmin
usuario
tintos
se nesesita que si juan david tiene los permisos admin y tintos cuando quiera añadir en la base de datos por medio del select solo muestre los roles no asignado asi que en este caso ya no va a mostrar admin y tintos sino solo usuario y superAdmin

yo he trabajado pero no consigo sino que me mustre los dos que tengo asiganados

Código MySQL:

select r.nombre from roles r, usuarios_roles ur where ur.usuarios_idusuarios=2 and   r.idroles = ur.roles_idroles
 

[leonardo_josue]

Hola danj:

Puedes utilizar la función NOT EXISTS O la función NOT IN para filtrar la información. En lo particular prefiero la primer opción, pues es mucho más versatil que utilizar una función IN. Sería más o menos así:

Código MySQL:

mysql> create table usuarios (idUsuario int, 
    -> nombre varchar(15));
Query OK, 0 rows affected (0.17 sec)
 
mysql> insert into usuarios values (1, 'Juan David'), (2, 'otro usuario');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from usuarios;
+-----------+--------------+
| idUsuario | nombre       |
+-----------+--------------+
|         1 | Juan David   |
|         2 | otro usuario |
+-----------+--------------+
2 rows in set (0.01 sec)
 
mysql> create table roles (idRol int, descripcion varchar(15));
Query OK, 0 rows affected (0.11 sec)
 
mysql> insert into roles values (1, 'super admin'), (2, 'admin'),
    -> (3, 'usuario'), (4, 'tintos');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> select * from roles;
+-------+-------------+
| idRol | descripcion |
+-------+-------------+
|     1 | super admin |
|     2 | admin       |
|     3 | usuario     |
|     4 | tintos      |
+-------+-------------+
4 rows in set (0.00 sec)
 
mysql> create table usuarios_roles (idUR int, idUsuario int, idRol int);
Query OK, 0 rows affected (0.17 sec)
 
mysql> insert into usuarios_roles values (1, 1, 2), (2, 1, 4), (3, 2, 1);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from usuarios_roles;
+------+-----------+-------+
| idUR | idUsuario | idRol |
+------+-----------+-------+
|    1 |         1 |     2 |
|    2 |         1 |     4 |
|    3 |         2 |     1 |
+------+-----------+-------+
3 rows in set (0.00 sec)
 
mysql> #para mostrar los roles que no han sido asignados al usuario 1
mysql> select * from roles r where not exists
    -> (select idRol from usuarios_roles ur
    -> where ur.idRol = r.idRol and ur.idUsuario = 1);
+-------+-------------+
| idRol | descripcion |
+-------+-------------+
|     1 | super admin |
|     3 | usuario     |
+-------+-------------+
2 rows in set (0.00 sec)
 
mysql> #para mostrar los roles que no han sido asignados al usuario 2
mysql> select * from roles r where not exists
    -> (select idRol from usuarios_roles ur
    -> where ur.idRol = r.idRol and ur.idUsuario = 2);
+-------+-------------+
| idRol | descripcion |
+-------+-------------+
|     2 | admin       |
|     3 | usuario     |
|     4 | tintos      |
+-------+-------------+
3 rows in set (0.00 sec)
 
mysql> #Mismo resultado que el anterior pero utilizando NOT IN
mysql> select * from roles r where r.idRol not in
    -> (select idRol from usuarios_roles ur
    -> where ur.idUsuario = 2);
+-------+-------------+
| idRol | descripcion |
+-------+-------------+
|     2 | admin       |
|     3 | usuario     |
|     4 | tintos      |
+-------+-------------+
3 rows in set (0.00 sec)
 

Finalmente también podrías hacer un LEFT JOIN y filtrar aquellos registros que te regresen NULL, más o menos así

Código MySQL:

mysql> select r.* from roles r left join usuarios_roles ur
    -> on r.idRol = ur.idRol and ur.idUsuario = 2 where ur.idRol is null;
+-------+-------------+
| idRol | descripcion |
+-------+-------------+
|     2 | admin       |
|     3 | usuario     |
|     4 | tintos      |
+-------+-------------+
3 rows in set (0.00 sec)
 

Dale un vistazo al script para ver si te puede servir.

Saludos
Leo.