CODEWITHSUNDEEP
mysqlsql
arderoma
arderoma

Reputation: 427

Filtering an inner join results

I have four tables.

usuarios

roles

permisos

roles_permisos (pivot table)

I need to get all the users and roles with the permission equal to 2. I'm not good with sql and I don't know how to achieve this. I guess I need an inner join between users and roles and from there filter the role with permission 2 from the pivot table

SELECT usuarios.nombre, roles.nombre
FROM usuarios
INNER JOIN roles ON roles.id = usuarios.rol_id

http://sqlfiddle.com/#!9/470356/16

PD: sorry, the examples are in spanish

Upvotes: 1

Views: 49

Answers (2)

LukStorms
LukStorms

Reputation: 29677

You can start from the table for the permissions (permisos).
Where the id equals 2, or the name (permiso) equals 'Editar'.

Then join the other tables on the correct id.
Till you get to the table with the users (usuarios)

SELECT 
  usuarios.nombre AS usuario_nombre
, roles.nombre AS role_nombre
FROM permisos
JOIN roles_permisos 
  ON roles_permisos.permisos_id = permisos.id
JOIN roles
  ON roles.id = roles_permisos.roles_id
JOIN usuarios
  ON usuarios.rol_id = roles.id
WHERE permisos.permiso = 'Editar';

Upvotes: 0

Barmar
Barmar

Reputation: 781937

Add another join with roles_permisos

SELECT usuarios.nombre, roles.nombre
FROM usuarios
INNER JOIN roles ON roles.id = usuarios.rol_id
INNER JOIN roles_permisos ON roles.id = roles_permisos.roles_id
WHERE roles_permisos.permisos_id = 2

Upvotes: 2

Related Questions