Select con left join. Consulta compleja...Para mí

[chelodelsur]

Hola nuevamente pidiendo ayuda.

Tengo una consulta con dos tablas mediante left join, pero no logro sacar el resultado que deseo.
Las tablas son:
Agentes: campos: id_agente, nombre_agente, empleador_agente, tipo_turno, estado.
Evaluaciones: campos: id_evaluacion, id_llamada, agente_evaluado, fecha_evaluacion.
Lo que necesito traer es: los agentes con sus datos de empleador, tipo turno, estado y la cantidad de evaluaciones hechas en un determinado periodo de tiempo en caso de no tener que me indique 0 evaluaciones.
Pues bien mi consulta hace todo menos, los agentes que NO tuvieron evaluaciones y es para mis efectos un dato necesario.

Código:

select  evaluaciones.agente_evaluado AS AGENTE,
           agentes.Empleador_agente as EMPRESA,
           agentes.Tipo_Turno as TURNO,
           agentes.estado As ESTADO, 
           count(IF(evaluaciones.id_llamada>=1,1,0)) AS EVALUACIONES 
           from `callcapture`.`agentes`
           left join callcapture.evaluaciones 
           on agentes.nombre_agente = evaluaciones.agente_evaluado 
           where agentes.nombre_Agente like '%'
           and evaluaciones.fecha_evaluacion >='2008/07/01'
           and evaluaciones.fecha_evaluacion <='2008/09/01'
           GROUP BY evaluaciones.agente_evaluado
           ORDER BY Empleador_agente ASC ,Tipo_Turno ASC, agente_evaluado ASC

Desde ya agradesco su siempre buena disposición a ayudar

Marcelo

[seyko]

Creo que puede ser por esto:
Un agente puede no tener evaluaciones, pero en tu select:

Código:

select  evaluaciones.agente_evaluado AS AGENTE
[...]

Si no tiene evaluacion no puedes coger el agente, deberias de coger este dato de la tabla de agentes

Pueba y comentas.

Un saludo

[jurena]

Chelodelsur,
tu problema, creo, es que estableces una condición de fechas que no puede cumplir ninguno de los no evaluados.
Si añades tras las condiciones de fecha:... OR fecha IS NULL, los verás, según creo. Debería quedar así:

and (evaluaciones.fecha_evaluacion >='2008/07/01'
and evaluaciones.fecha_evaluacion <='2008/09/01') OR evaluaciones.fecha_evaluacion IS NULL

No lo he probado.

Aclaración: piensa que, al hacer el left join, te cruza todos los agentes, pero los datos de la tabla evaluaciones de algunos agentes no existen y cuando se realiza el cruce, aparecen como NULL. Tenlo en cuenta si quieres listar todos los agentes y en tu consulta condicionas campos de esa tabla evaluaciones.

[chelodelsur]

Gracias muchachos por su ayuda

Pero no sale.
Haber Jurena hice lo de del where, y claro lista los NO evaluados pero los agrupa en un "nombre agente" NULL, o sea me dice agente NULL 22 evaluaciones, que es la cantidad de agentes sin evaluar en ese rango de fechas.
Seyko, al cambiar los agentes del select de la tabla evaluaciones a la de agentes, el valor NULL se me cambia por el del primer agente sin evaluar del listado y le asigna a ese nombre las 22 evaluaciones faltantes.

Alguna idea de como salir de este atoro???

Como siempre Gracias por adelantado por su simpre generosa ayuda

Marcelo

[jurena]

No puedes agrupar por evaluaciones.agente_evaluado, puesto que en los casos en que no hay evaluación, ese campo es nulo; de ahí el null que te sale como valor agrupado y el total de 22.
agrupa por agentes.nombre_agente. Además de lo que te dije en el anterior post sobre las fechas, agrupa así:

GROUP BY agentes.nombre_agente

creo que funcionará. A mí me funciona en una tabla parecida a la tuya...

[chelodelsur]

mmmm, ya comienzo a frustarme.

Mira hice algunas modificaciones al codigo, para ir aislando donde esta el problema. El siguente codigo me lista sin problema lo que quiero, los 55 agentes, y contabiliza las evaluaciones, a los agentes que no tienen, le indica que tienen 0 evaluaciones. esto es lo que necesito.

Código:

SELECT  agentes.nombre_agente as AGENTE,
       agentes.empleador_agente as Empresa,
       agentes.tipo_turno as TURNO,
       agentes.estado as ESTADO,
       count(IF(agentes.id_agente='',0,evaluaciones.id_evaluacion)) AS EVALUACIONES       
FROM callcapture.agentes
LEFT JOIN callcapture.evaluaciones
ON agentes.nombre_agente = evaluaciones.agente_evaluado
GROUP BY agentes.nombre_agente;

Pero el problema empieza cuando aplico filtro en el where , al filtar por fecha, por ejemplo probé con una fecha furura, (septiembre), se supone que debe listarme todos los agentes con 0, (los 55) pero me lista solo 22, que son los que nunca han tenido evaluacion. de todos modos es muy extraño el resultado. Revisaré las datos de la tabla en busca de algún error.

[chelodelsur]

Ya revise las tablas, encontré algunos errores, pero nada aun no salgo del problema.
Según veo el problema es que cuando busco fechas mayores a la última cargada en la tabla evaluaciones con la condicion Where, busca un dato que no existe, eso por un lado, al aplicar el Or Is Null, claro me trae los datos, pero (y aquí mi gran duda) solo los agentes que no tienen evaluaciones, o sea trae los agentes (tabla agente) que no estan en la tabla evaluaciones.agente_evaluado. Cuando hago la consulta en un rango de fechas ya pasado la consulta funciona bien.
Pero mi problema es que esta consulta pretende ser usada para listar los agentes y evaluarlos, partiendo como prioridad los que no tienen ninguna, entonces me pongo en el caso que por ejemplo a pricipios de mes (1, o 2 del mes) hagan la consulta y no le liste ningun agente, porque en la tabala evaluaciones aún no se a cargado datos para esa fecha.
Alguna idea o sugerencia ????

Gracias.

Código:

SELECT agentes.nombre_agente as AGENTE_agentes,               
       agentes.empleador_agente as Empresa,
       agentes.tipo_turno as TURNO,
       agentes.estado as ESTADO,
       count(IF(agentes.id_agente='',0,evaluaciones.id_evaluacion)) AS EVALUACIONES       
FROM callcapture.agentes
LEFT JOIN callcapture.evaluaciones
ON agentes.nombre_agente = evaluaciones.agente_evaluado
WHERE evaluaciones.fecha_evaluacion >='2008-06-01' 
and evaluaciones.fecha_evaluacion <= '2008-07-01' or evaluaciones.fecha_evaluacion IS null
GROUP BY agentes.nombre_agente;

[jurena]

Esto hará lo que quieres, aunque no sé cuál será su rendimiento. Los nombres de los campos no son los mismos que los tuyos (hice una pequeña base para probar y adopté otros; las relaciones las hago con agentes.id y evaluaciones.idagente
La clave está en hacer la consulta y luego establecer una relación left join con el resultado de la consulta. Como decías, con las condiciones de fecha futuras no encuentra a los agentes que tengan evaluaciones. Ahora busca primero con un inner join, sin necesidad de usar el OR de la fecha y luego cruza esos resultados con las tablas agente y evaluaciones ya con un left join.

SELECT agentes.agente, ifnull( T1.eval, '0' ) evaluaciones
FROM agentes
LEFT JOIN (

SELECT a.id, COUNT( idagente ) eval
FROM agentes a
INNER JOIN `evaluaciones` e ON a.id = e.idagente
WHERE e.fecha
BETWEEN '2008/08/25'
AND '2008/08/30'
GROUP BY a.id
)T1 ON agentes.id = T1.id
LEFT JOIN evaluaciones ON evaluaciones.idagente = agentes.id
GROUP BY agentes.id

Adapta esto a los nombres de tus campos y creo que ahora funcionará.
OBSERVA que yo uso BETWEEN ... AND... entre las fechas, y la función IFNULL para poner los ceros.

[chelodelsur]

Hola Jurena

Desde ya gracias por las repuestas, disculpa la patudez (descaro) pero le he dado vueltas y estoy bastante liado con el reemplazo de los nombres de campos, creo por que no logro seguir la logica del select que me has enviado (soy muy básico en mis conocimientos) te adjunto mis tablas haber si por enesima vez me hechas una mano

Código:

   CREATE TABLE `agentes` (                                                                                                                                                                                                                                                                                                                                                                                                                                                       
     `id_agente` int(11) NOT NULL auto_increment,                                                                                                                                                                                                                                                                                                                                                                                                                                 
     `Nombre_Agente` char(35) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                       
      `Empleador_Agente` char(35) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                    
      Tipo_Turno` char(15) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                          
      `Estado` char(10) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                              
      `Direccion_Particular` char(60) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                
      `Rut_Agente` char(12) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                          
      `Fono_Celular` char(10) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                        
      `Fono_Red_Fija` char(10) default NULL,                                                                                                                                                                                                                                                                                                                                                                                                                                       
       PRIMARY KEY  (`id_agente`)                                                                                                                                                                                                                                                                                                                                                                                                                                                   
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1

[chelodelsur]

Y esta es la otra tabla.
Bien la tabla agentes es una tabla de administarción del personal, en la que e stan cargados sus datos de habilidades, empleador, turnos, datos personales, etc.
La tabla evaluaciones es alimentada por un formulario donde mediante una serie de parametros se evalúa el cumplimiento de metas de los agentes.
La cunsulta que necestito debe listarme a todos los agentes, su empleador, su tipo turno, su estado (activo o retirado) y la cantidad de evaluaciones que se le han efectuado dentro de un periodo,

esa es la historia, espero puedas darme una mano

Gracias

Código:

create  table `evaluaciones`(
`ID_EVALUACION` int(11) NOT NULL auto_increment,
`ID_LLAMADA` varchar(10) default NULL,  
`AGENTE_EVALUADO` varchar(35) default NULL, 
`FECHA_EVALUACION` varchar(15) default NULL,  
`HORA_EVALUACION` varchar(10) default NULL,  
`TIPO_EVALUACION` varchar(20) default NULL, 
 `NOMBRE_EVALUADOR` varchar(35) default NULL, 
PRIMARY KEY  (`ID_EVALUACION`) 
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1” ))

[jurena]

Marcelo,
te diré antes que nada que no me gusta mucho cómo estableces la relación entre las tablas agentes y evaluaciones por medio de campos de texto, Nombre_agente y agente_evaluado, respectivamente. Pero lo que no es aceptable es poner la fecha_evaluacion como varchar. Tendría que ser un campo de tipo date; y tampoco veo razonable lo de la hora como varchar, cuando la hora debería ser de tipo time, y si siempre va a ir unida a la fecha, puesto que es fecha y hora de la evaluación, deberían aparecer los datos de fecha y hora en un único campo de tipo datetime. Debes cuidar esas cosas si quieres que luego todo sea más fácil y la tabla ofrezca mejor rendimiento. Pero vamos a por la consulta que quieres.
Prueba con esto:

SELECT a.Nombre_agente, a.Empleador_agente, a.tipo_turno, a.Estado, IFNULL( T1.eval, '0' ) evaluaciones
FROM agentes a
LEFT JOIN (SELECT a.Nombre_agente na, COUNT( a.Nombre_agente ) eval
FROM agentes a
INNER JOIN `evaluaciones` e ON a.Nombre_agente = e.agente_evaluado
WHERE e.fecha_evaluacion
BETWEEN '2008/08/01'
AND '2008/08/30'
GROUP BY a.Nombre_agente
)T1 ON a.Nombre_agente = T1.na
ORDER BY a.Nombre_agente

HE EDITADO para hacer algunas correcciones, puesto que de la tabla evaluaciones sólo necesitas la cuenta de evaluaciones, no es necesario un segundo left join y tampoco una segunda agrupación...

[chelodelsur]

GRANDE GURUUUU¡¡¡¡

Gracias Jurena, quedó perfecta, bueno y muchas gracias por los comentarios sobre las tablas.

no me gusta mucho cómo estableces la relación entre las tablas agentes y evaluaciones por medio de campos de texto, Nombre_agente y agente_evaluado,

bueno esa esta facil de solucionar, le agregaré el Id_agente a la tabla evaluaciones, para hacer la relación por medio de este campo.

Pero lo que no es aceptable es poner la fecha_evaluacion como varchar. Tendría que ser un campo de tipo date; y tampoco veo razonable lo de la hora como varchar, cuando la hora debería ser de tipo time, y si siempre va a ir unida a la fecha, puesto que es fecha y hora de la evaluación, deberían aparecer los datos de fecha y hora en un único campo de tipo datetime.

Bueno esta me costará un poco más ya que los datos son gatillados por una aplicación externa, y vienen en ese formato, lo intenté pero me genera algunos problemas , pero sin duda tienes toda la razón

Muchisimas gracias