After giving a thousand turns to my problem, to my E/R that I had supposedly badly assembled. It hasn't, I just needed to create a proper query, but I run into the following problem:
I have this query:
Select *
FROM contratan c
RIGHT join asistencia a
ON a.usuario = c.usuario
RIGHT JOIN actuacion ac
ON a.codAsistencia = ac.asistencia
WHERE c.usuario = 17
AND c.codContrato in (select MAX(codContrato) from contratan where usuario = 17)
order by c.usuario
This query makes me a history of all the data referenced to that user. but I also need to calculate the remaining minutes that user has left on their voucher. I would do it with this other query:
Select sum(minutos) FROM bonos, contratan where contratan.bono = bonos.codBono and contratan.usuario = 17
but I also need to check the minutes that have been used in each attendance, I do it with this other query.
Select sum(tiempoEmpleado) FROM actuacion, asistencia where actuacion.asistencia = asistencia.codAsistencia and asistencia.usuario = 17
The question is, how could I add these other two in the first query??. What is this for? It is to make a complete history of the user, what he buys, the minutes he has left, how he has spent those minutes and the assistance he has requested.
I've tried with subqueries in the select, but I can't... I appreciate any help. Attached the structure of my database:
users table:
CREATE TABLE `users` (
`id` bigint(20) UNSIGNED NOT NULL,
`nif` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`nombre` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`direccion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`perfil` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'usuario',
`telefono` int(11) DEFAULT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Volcado de datos para la tabla `users`
--
INSERT INTO `users` (`id`, `nif`, `nombre`, `email`, `email_verified_at`, `password`, `direccion`, `perfil`, `telefono`, `remember_token`, `created_at`, `updated_at`) VALUES
(1, '45921676z', 'David Serrano Alonso', '[email protected]', NULL, '$2y$10$nZcz5O3MaE/F/ML0QSC7M.1/fe.6QYdd11Faa1Wnsh.E4TiZR3xcW', NULL, 'usuario', 652138927, NULL, '2020-11-20 09:19:46', '2020-11-20 09:19:46'),
(2, '45921676z', 'Admin', '[email protected]', '2020-11-20 10:29:35', '$2y$10$wxOoOy1hlc8JYnozGKy.ReNwT3Q24akiltSyfrGYJ39wjud72lH7a', NULL, 'tecnico', NULL, NULL, NULL, NULL);
contract table
--
-- Base de datos: `bonosat`
--
-- --------------------------------------------------------
--
-- Estructura de tabla para la tabla `contratan`
--
CREATE TABLE `contratan` (
`codContrato` int(10) UNSIGNED NOT NULL,
`fecha` datetime NOT NULL,
`bono` int(10) UNSIGNED NOT NULL,
`tiempoRestanteBono` int(10) UNSIGNED NOT NULL,
`bonoSolicitado` int(10) UNSIGNED NOT NULL,
`tiempoSolicitado` int(10) UNSIGNED NOT NULL,
`activo` tinyint(4) NOT NULL DEFAULT 0,
`usuario` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Volcado de datos para la tabla `contratan`
--
INSERT INTO `contratan` (`codContrato`, `fecha`, `bono`, `tiempoRestanteBono`, `bonoSolicitado`, `tiempoSolicitado`, `activo`, `usuario`) VALUES
(11, '2020-11-23 11:38:20', 6, 10, 1, 30, 0, 1);
--
-- Índices para tablas volcadas
--
--
-- Indices de la tabla `contratan`
--
ALTER TABLE `contratan`
ADD PRIMARY KEY (`codContrato`),
ADD KEY `contratan_usuario_foreign` (`usuario`),
ADD KEY `contratan_bono_foreign` (`bono`),
ADD KEY `contratanBonoSoli_bono` (`bonoSolicitado`);
bonuses
CREATE TABLE `bonos` (
`codBono` int(10) UNSIGNED NOT NULL,
`tipo` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`minutos` int(10) UNSIGNED NOT NULL,
`precio` double(5,2) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Volcado de datos para la tabla `bonos`
--
INSERT INTO `bonos` (`codBono`, `tipo`, `minutos`, `precio`, `created_at`, `updated_at`) VALUES
(1, '1/2H', 30, 20.00, NULL, NULL),
(2, '1H', 60, 40.00, NULL, NULL),
(3, '5H', 300, 175.00, NULL, NULL),
(4, '10H', 600, 330.00, NULL, NULL),
(5, '24H', 1200, 600.00, NULL, NULL),
(6, '10Min', 10, 9.00, NULL, NULL);
attendance
CREATE TABLE `asistencia` (
`codAsistencia` int(10) UNSIGNED NOT NULL,
`fecha` datetime NOT NULL,
`mensaje` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`usuario` bigint(20) UNSIGNED NOT NULL,
`estado` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pendiente',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Volcado de datos para la tabla `asistencia`
--
INSERT INTO `asistencia` (`codAsistencia`, `fecha`, `mensaje`, `usuario`, `estado`, `created_at`, `updated_at`) VALUES
(2, '2020-11-23 11:25:29', 'Prueba Histórico', 1, 'pendiente', NULL, NULL),
(3, '2020-11-23 12:32:03', 'prueba', 1, 'pendiente', NULL, NULL);
--
-- Índices para tablas volcadas
--
--
-- Indices de la tabla `asistencia`
--
ALTER TABLE `asistencia`
ADD PRIMARY KEY (`codAsistencia`),
ADD KEY `asistencia_usuario_foreign` (`usuario`);
When executing my first query
If I add the other two queries:
Select (Select sum(minutos)FROM bonos, contratan where contratan.bono = bonos.codBono and contratan.usuario = 17), (Select sum(tiempoEmpleado) FROM actuacion, asistencia where actuacion.asistencia = asistencia.codAsistencia and asistencia.usuario = 17)
FROM contratan c
RIGHT join asistencia a ON a.usuario = c.usuario
RIGHT JOIN actuacion ac ON a.codAsistencia = ac.asistencia
WHERE c.usuario = 17
AND c.codContrato in (select MAX(codContrato) from contratan where usuario = 17) order by c.usuario
The result of joining these two select is this:
As you can see, it duplicates my data, and apart from that, well, it doesn't get the other data I need, but that's adding the columns I need, what I need is to see the time spent per performance and the remaining time of the bonus after
Database execution
Adding the subqueries to your query is just adding the queries as columns
Now what is the problem? If the original query returned two records, the new query will return two records, and the data it will bring back is always the same, since the only relationship between them is the user.
Note that the number 17 can be replaced by any variable.
To make joins like this, the method I use is to add subqueries in the join, and then apply it in the select.
Leave the queries in a LEFT JOIN, because I don't know if the records should exist in the bonus and performance tables, you can change them anyway.