Query between 4 mysql tables with foreign keys (foreign key)

Question

Query between 4 mysql tables with foreign keys (foreign key)

Navigation

#1 by (0 votes)

1

I have these tables in my database to assign roles to my users:

    tbl usuario
    id
    nombre
    contraseña
    correo
    fk_rol
--------------------
    tbl rol
    id_rol
    nombre
    descripcion
--------------------
    tbl pagina
    id_pagina
    url
---------------------
    tbl rol_pagina
    fk_rol
    fk_pagina

I need a query that returns the name of a user and the pages to which it has access, this is the query that I have at the moment, it shows me that url has access every rol , but I am missing specify which user

select r.nombre, p.Url from rol r, pagina p, rol_pagina n, usuario u 
where r.id_rol = u.fk_rol
and n.fk_rol = r.Id_rol 
and n.fk_pagina = p.id_pagina
and u.fk_rol = 1

Tables:

create table Usuario (
   id_usuario int not null auto_increment,
   nombre varchar(30) not null,
   Contrasena varchar(30) not null,
   id_rol int not null,
   primary key (id_usuario),
   constraint fk_rol foreign key (id_rol)
      references rol(id_rol));

create table rol (
   id_rol int not null auto_increment,
   nombre varchar(30) not null,
   primary key (id_rol));

create table pagina (
   id_pagina int not null auto_increment,
   Url varchar(100) not null,
   primary key (id_pagina));

create table rol_pagina (
   id_rol int not null,
   id_pagina int not null,
   constraint fkrol foreign key (id_rol)
      references rol(id_rol),
   constraint fkpagina foreign key (id_pagina)
      references pagina(id_pagina));

mysql sql sql-server

asked by FeRcHo 21.01.2017 в 21:50

source

1 answer

Insert data from java in MySQL Local name collision

score 0 · Accepted Answer

To combine the tables with their FOREIGN KEY s you have to use a JOIN.

The following SELECT gives you all users and the pages they have access to for the role with id_rol = 1

SELECT r.nombre, p.Url
FROM Usuario AS u INNER JOIN rol AS r ON u.fk_rol = r.id_rol
INNER JOIN rol_pagina AS n ON n.fk_rol = r.id_rol
INNER JOIN pagina AS p ON n.fk_pagina = p.id_pagina
WHERE u.fk_rol = 1 ;

To obtain all the pages to which a user (searched by id) has access, you use:

SELECT r.nombre, p.Url
FROM Usuario AS u INNER JOIN rol AS r ON u.fk_rol = r.id_rol
INNER JOIN rol_pagina AS n ON n.fk_rol = r.id_rol
INNER JOIN pagina AS p ON n.fk_pagina = p.id_pagina
WHERE u.id_usuario = ? ;

To search the pages of a user identified by name and a role with id_rol = 1 you use:

SELECT r.nombre, p.Url
FROM Usuario AS u INNER JOIN rol AS r ON u.fk_rol = r.id_rol
INNER JOIN rol_pagina AS n ON n.fk_rol = r.id_rol
INNER JOIN pagina AS p ON n.fk_pagina = p.id_pagina
WHERE u.nombre = ? AND u.fk_rol = 1 ;

I recommend a basic SQL tutorial like this.

Start with learning what SELECT means and for WHERE is used.

More information about JOIN