New question

Question:

Date: 23-03-2015 20:11:20 (In Spanish)

¿Cómo unir tres tablas?[Resolved]

Hola a todos

Quería consultarles como unir 3 tablas con INNER JOIN

Un usuario, tiene sus datos en la tabla usuario, un rol en la tabla usuario_rol, un estado de su cuenta en la tabla usuario_estado

Y quiero listar todos los usuarios con su perfil completo cuyos datos están en las 3 tablas.

Un usuario tiene un solo rol y un solo estado posible

Este seria el DER que arme



Estructura:

No sé si las calves foraneas estan bien


-- phpMyAdmin SQL Dump
-- version 4.3.11.1
-- http://www.phpmyadmin.net
--
-- Servidor: 127.0.0.1
-- Tiempo de generación: 24-03-2015 a las 06:45:11
-- Versión del servidor: 5.6.17
-- Versión de PHP: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de datos: `intranet`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `usuario`
--

CREATE TABLE IF NOT EXISTS `usuario` (
  `id` int(10) unsigned NOT NULL,
  `usuario_rol_id` int(10) unsigned NOT NULL,
  `nickName` varchar(50) NOT NULL,
  `nombreApellido` varchar(200) NOT NULL,
  `Email` varchar(150) NOT NULL,
  `Password` varchar(100) NOT NULL,
  `fechaNacimiento` datetime DEFAULT NULL,
  `FechaCreacion` datetime NOT NULL,
  `estadoFechal` datetime NOT NULL COMMENT 'fecha del cambio del estado del usuario',
  `usuario_estado_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `usuario`
--

INSERT INTO `usuario` (`id`, `usuario_rol_id`, `nickName`, `nombreApellido`, `Email`, `Password`, `fechaNacimiento`, `FechaCreacion`, `estadoFechal`, `usuario_estado_id`) VALUES
(1, 1, 'webmaster', 'Walter', 'admin@dominio.com', '123456', '1973-01-18 00:00:00', '2015-03-24 00:00:00', '2015-03-24 00:00:00', 2);

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `usuario_estado`
--

CREATE TABLE IF NOT EXISTS `usuario_estado` (
  `id` int(10) unsigned NOT NULL,
  `estadoValor` int(11) NOT NULL,
  `estadoLeyenda` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `usuario_estado`
--

INSERT INTO `usuario_estado` (`id`, `estadoValor`, `estadoLeyenda`) VALUES
(1, 1, 'Activar'),
(2, 2, 'Activado'),
(3, 3, 'Inactivo'),
(4, 4, 'Bloqueado');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `usuario_rol`
--

CREATE TABLE IF NOT EXISTS `usuario_rol` (
  `id` int(10) unsigned NOT NULL,
  `privilegioNivel` int(10) unsigned NOT NULL COMMENT 'Numero de nivel',
  `privilegioLeyenda` varchar(100) NOT NULL COMMENT '''texto descriptivo de la leyenda''',
  `privilegioDescripcion` varchar(254) NOT NULL COMMENT '''Descripcion del previlegio'''
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `usuario_rol`
--

INSERT INTO `usuario_rol` (`id`, `privilegioNivel`, `privilegioLeyenda`, `privilegioDescripcion`) VALUES
(1, 1, 'Desarrollador', 'Administracion del sitio web por el desarrollador de la app'),
(2, 2, 'Administardor', 'Administardor del sitio'),
(3, 3, 'Usuario', 'Usuario final');

--
-- Índices para tablas volcadas
--

--
-- Indices de la tabla `usuario`
--
ALTER TABLE `usuario`
  ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`), ADD KEY `fk_usuario_rol_usuario_idx` (`usuario_rol_id`), ADD KEY `fk_usuario_usuario_estado1_idx` (`usuario_estado_id`);

--
-- Indices de la tabla `usuario_estado`
--
ALTER TABLE `usuario_estado`
  ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`);

--
-- Indices de la tabla `usuario_rol`
--
ALTER TABLE `usuario_rol`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT de las tablas volcadas
--

--
-- AUTO_INCREMENT de la tabla `usuario`
--
ALTER TABLE `usuario`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT de la tabla `usuario_estado`
--
ALTER TABLE `usuario_estado`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT de la tabla `usuario_rol`
--
ALTER TABLE `usuario_rol`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4;
--
-- Restricciones para tablas volcadas
--

--
-- Filtros para la tabla `usuario`
--
ALTER TABLE `usuario`
ADD CONSTRAINT `fk_usuario_rol_usuario` FOREIGN KEY (`usuario_rol_id`) REFERENCES `usuario_rol` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_usuario_usuario_estado1` FOREIGN KEY (`usuario_estado_id`) REFERENCES `usuario_estado` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;





El siguiente codigo me funcionaria:
SELECT * FROM usuario AS us, usuario_rol AS usr, usuario_estado AS usest WHERE us.usuario_rol_id=usr.id AND us.usuario_estado_id=usest.id 


pero lo quería hacer de la manera que he explicado anteriormente.

Arme este query pero no me funciona
(select * from usuario AS us
INNER JOIN 
usuario_rol as usr
ON
us.usuario_rol_id= usr.id)
UNION 
(select * from usuario_estado INNER JOIN usuario ON usuario.usuario_estado_id =usuario_estado.id );


MySQL dice:
#1222 - El comando SELECT usado tiene diferente numero de columnas



Desearía una ayuda y en lo posible una explicación sobre como se debería realizar

Muchas gracias
Saludos
Tags: DER - MySQL - MySQL Developing - Query - Question - SQL Votes: 1 - Answers: 7 - Views: 31 Share on: Google Facebook Twitter LinkedIn Link
 

Answers:

  • Date: 24-03-2015 06:13:20 Hola Walter, muy buena la explicación de tu pregunta, da gusto leer algo cargado de esta forma (excelente).

    Yendo al problema, te comento que no te esta funcionando tu QUERY porque la directiva UNION espera que los resultados a unir tengan la misma cantidad de columnas (y en este caso no es así, si ejecutas las QUERY por separado veras que devuelven distinta cantidad de columnas).

    Para relacionar las 3 tablas debes hacer 2 INNER JOIN en la misma QUERY, por ejemplo:

    SELECT *
    FROM usuario AS us
    INNER JOIN usuario_rol AS us_ro
        ON (us.usuario_rol_id= us_ro.id)
    INNER JOIN usuario_estado AS us_es
        ON (us.usuario_estado_id = us_es.id);


    Ahora bien, el QUERY anterior trae la unión de las 3 tablas en base a sus claves foraneas, junto con todos sus campos (ya que use SELECT *), y sin ningun tipo de filtro. En el siguiente ejemplo vemos que solo traigo algunos datos de forma selectiva (SELECT) y filtro por usuarios activos (WHERE):

    SELECT
        us.nombreApellido,
        us.email,
        us_ro.privilegioLeyenda,
        us_es.estadoLeyenda
    FROM usuario AS us
    INNER JOIN usuario_rol AS us_ro
        ON (us.usuario_rol_id= us_ro.id)
    INNER JOIN usuario_estado AS us_es
        ON (us.usuario_estado_id = us_es.id)
    WHERE us_es.estadoValor = 2;


    Espero que mi respuesta te sea de ayuda, luego me cuentas como te fue.

    Saludos,
    Fernando
      Votes: 2 - Link answer
     
  • Date: 24-03-2015 07:57:01 Ese es mi trabajo de todos los dias..   Votes: -1 - Link answer
     
  • Date: 24-03-2015 21:43:29 Muchas gracias Fernando por tu respuesta
    Que bueno que te haya gustado la manera de hacer la pregunta.

    Ya estuve mal de entrada en utilizar UNION

    Voy a probar con INNER JOIN e interpretar su lógica ;)

    Por otro lado, como he mencionado en el post inicial.
    en vez de usar INNER JOIN, esta bien usar esta Query?

    SELECT * FROM usuario AS us, usuario_rol AS usr, usuario_estado AS usest WHERE 
    us.usuario_rol_id=usr.id AND us.usuario_estado_id=usest.id 
    


    Saludos
      Votes: 0 - Link answer
     
  • Date: 26-03-2015 14:29:43 Hola Walter, si, las consultas son similares, deberían devolver los mismos resultados y el plan de ejecución debería ser igual, puedes verificar esto anteponiendo la directiva EXPLAIN al SELECT y comparando los resultados para ambos casos.

    En lo particular, yo prefiero utilizar INNER JOIN, ya que en algunos motores de bases de datos se resuelve de una manera más eficiente.

    Saludos,
    Fernando
      Votes: 1 - Link answer
     
  • Date: 27-03-2015 08:59:56 El UNION no funciona, porque lo estas haciendo de 2 consultas con diferentes cantidad de campos en el resultado. Esto es porque la tabla de roles (4 columnas) y estados (3 columnas) no tiene la misma cantidad de columnas. Hay una foma de darle una vuelta a esto creando campos adicionales para engañar a la DB en el SELECT de esta forma:

    SELECT 
        id,
        estadoValor,
        estadoLeyenda,
        'columna_inexistente'
    FROM
        usuario_estado;


    La consulta con JOIN, sería la siguiente.

    SELECT 
        *
    FROM
        usuario u
            LEFT JOIN
        usuario_rol ur ON u.usuario_rol_id = ur.id
            LEFT JOIN
        usuario_estado ue ON u.usuario_estado_id = ue.id;
      Votes: 2 - Link answer
     
  • Date: 27-03-2015 11:18:35 Diego Rivarola muchas gracias por tu respuesta, voy a probar lo que me has propuesto.
    en cuanto a performance se refiere, realizar ese tipo de consulta no le afecta?
      Votes: 0 - Link answer
     
  • Date: 27-03-2015 11:57:02 No, para nada. Solo tendrías que estar atento a la performance si en la tabla usuarios tuvieras millones de registros, GB de datos...
    En la query se están utilizando los indices de las claves foráneas, lo que esta bien, pero si en el WHERE ubicas al usuario por Email, por ejemplo, te convendría crear un indice en ese campo, para que MySQL no haga un FULL SCAN de la tabla.

    Pero en el caso hipotetico de que tuvieras una tabla con millones de registros, lo cual en la mayoría de los casos son tablas que se utilizan para almacenar datos históricos o logs, convendría analizar la query con EXPLAIN (en el caso de MySQL, para otros motores de BD necesitaras otras sentencias para revisar el plan de ejecución), revisar indices, tal vez particionar la tabla, etc
      Votes: 1 - Link answer
     
To actively participate in the community first must authenticate, enter the system.Sign In