A
I think I got the consultation you wanted.A few notes for future questions you want to ask.UNOYou don't need to pass the complete structure of the tables in your database, just include the fields more relevant, that is, those who serve to relate the tables between themselves and those you want to come out in the final result of the consultation.DNo one will ask you to pass the actual data on your database, either, but you can provide us with a minimum set of data with fictitious data. Indicating the result you have obtained so far and the result you want to get.Having said all this, I have simplified your tables to stay with the relevant columns and I have generated a small set of data to prove I got the data you want. The following are:CREATE TABLE LLAMADA (
id int(10) UNSIGNED NOT NULL,
nomape varchar(100) NOT NULL,
id_teleoperadora int(11) UNSIGNED DEFAULT NULL,
id_estado int(11) UNSIGNED NOT NULL,
PRIMARY KEY (id));
CREATE TABLE CITA (
id int(10) UNSIGNED NOT NULL,
id_llamada int(10) UNSIGNED NOT NULL,
id_estado int(10) UNSIGNED NOT NULL,
PRIMARY KEY (id));
CREATE TABLE CITA_ESTADO (
id int(10) UNSIGNED NOT NULL,
descripcion varchar(100) NOT NULL,
PRIMARY KEY (id));
CREATE TABLE USERS (
id int(10) UNSIGNED NOT NULL,
id_empleado varchar(255) NOT NULL,
nombre varchar(100) NOT NULL,
PRIMARY KEY (id));
INSERT INTO CITA_ESTADO (id, descripcion) VALUES
(1, 'NUEVA'),
(2, 'CONFIRMADA'),
(3, 'PENDIENTE'),
(4, 'NULA'),
(5, 'APLAZADA'),
(6, 'AUSENTE'),
(7, 'VENTA');
INSERT INTO CITA (id, id_llamada, id_estado) VALUES
(1, 1, 2),
(2, 2, 2),
(3, 8, 54),
(4, 4, 6),
(5, 77, 1),
(6, 6, 5),
(7, 88, 1),
(8, 8, 7),
(9, 9, 7),
(10, 10, 7),
(11, 21, 4);
INSERT INTO LLAMADA (id, nomape, id_teleoperadora, id_estado) VALUES
(1, 'Ricardo Rubio', 1, 5),
(2, 'Ricardo Rubio', 1, 5),
(3, 'Ricardo Rubio', 1, 3),
(4, 'Ricardo Rubio', 1, 5),
(5, 'Ricardo Rubio', 1, 8),
(6, 'Ricardo Rubio', 2, 5),
(7, 'Ricardo Rubio', 2, 9),
(8, 'Ricardo Rubio', 2, 5),
(9, 'Ricardo Rubio', 2, 5),
(10, 'Ricardo Rubio', 2, 5);
INSERT INTO USERS (id, id_empleado, nombre) VALUES
(1, 'TEST01', 'Trujillo, Juan'),
(2, 'TEST02', 'Luque, Ignacio');
The query that gets the appointments assigned by a concrete teleoperator grouped by their description would beSELECT U.ID, U.NOMBRE, CONCAT(CE.DESCRIPCION, "(", CE.ID, ")") AS ESTADO, COUNT(C.ID)
FROM USERS U
JOIN LLAMADA L ON U.ID = L.ID_TELEOPERADORA
JOIN CITA C ON L.ID = C.ID_LLAMADA
JOIN CITA_ESTADO CE ON C.ID_ESTADO = CE.ID
WHERE U.ID_EMPLEADO = 'TEST01'
AND L.ID_ESTADO = 5
GROUP BY U.ID, U.NOMBRE, CE.DESCRIPCION
ORDER BY U.NOMBRE, CE.DESCRIPCION;
For my little data game, with only two employees and one client, I would getID NOMBRE ESTADO COUNT(C.ID)
1 Trujillo, Juan AUSENTE(6) 1
1 Trujillo, Juan CONFIRMADA(2) 2
Note that I have not used the LLAMADA_ESTADO table because I do not need the description of the state of the call in principle. I wouldn't need to compose with the USERS table either if I already know the id of the teleoperator but I've included it if you wanted to search for your id instead of your id.If we already have the id of the user and do not need to recover more data from the USER table, we can simplify the query asSELECT L.ID_TELEOPERADORA, CE.DESCRIPCION, COUNT(C.ID)
FROM LLAMADA L
JOIN CITA C ON L.ID = C.ID_LLAMADA
JOIN CITA_ESTADO CE ON C.ID_ESTADO = CE.ID
WHERE L.ID_TELEOPERADORA = 1
AND L.ID_ESTADO = 5
GROUP BY L.ID_TELEOPERADORA, CE.DESCRIPCION
ORDER BY CE.DESCRIPCION;
ObtainingID_TELEOPERADORA DESCRIPCION COUNT(C.ID)
1 AUSENTE 1
1 CONFIRMADA 2
Which is very much like your required result.Try the query and if your actual data present some peculiarity that I have not contemplated, edit your question - trying to apply my two notes, to simplify it - and add that information.
