Problem in MySQL Select query

Question

Problem in MySQL Select query

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a problem in a Query in MySQL, I currently have two tables, one of employees and one of places visited by that employee by date, the goal is to obtain through a query the list of all employees and the last place they visited, so far so good, the problem is that some employees have no movement and therefore I can not include them in my final result, I will attach images of my tables and my current query, I hope you can support me. Thank you very much.

The Query used is the following:

SELECT tblempleados.Id AS idEmp, 
tblempleados.Nombre AS NombreEmpleado, 
tblmovimientos.Id AS IdUltimoMov, 
tblmovimientos.Area, 
tblmovimientos.FechaMovimiento AS Fecha,
tblmovimientos.IdCargo as idCargo
FROM (bd_secjo.tblmovimientos 
inner JOIN bd_secjo.tblempleados ON tblempleados.Id=tblmovimientos.IDEmpleado)
WHERE tblmovimientos.FechaMovimiento
IN (SELECT MAX(FechaMovimiento) FROM bd_secjo.tblmovimientos GROUP BY IdEmpleado)

mysql sql query select

asked by Carlos Daniel Zárate Ramírez 05.09.2018 в 20:17

source

2 answers

Preloader reappears after refreshing the page Given 2 lists generate 2 new lists with the elements that are in both and with those that are only in one

score 1 · Answer 1

First you have to make a subquery that throws the last movements of the collaborators and then you make a left join with the rest of the tables

with ultimo_mov (IdEmpleado,FechaMovimiento) AS
(SELECT IdEmpleado, MAX(FechaMovimiento) FROM bd_secjo.tblmovimientos GROUP BY IdEmpleado)
SELECT tblempleados.Id AS idEmp, 
tblempleados.Nombre AS NombreEmpleado, 
ultimo_mov.Id AS IdUltimoMov, 
ultimo_mov.Area, 
ultimo_mov.FechaMovimiento AS Fecha,
ultimo_mov.IdCargo as idCargo
FROM bd_secjo.tblempleados 
LEFT JOIN ultimo_mov ON (tblempleados.Id=ultimo_mov.IDEmpleado)

De esta manera se incluyen todos los valores de la tabla tblempleados

score 0 · Answer 2

First you have to make your selection to the table tblemployed because you want them all not?

SELECT tb_emp.Id AS idEmp, 
tb_emp.Nombre AS NombreEmpleado, 
from bd_secjo.tblempleados as tb_emp

That will bring all the employees, then you have to make a selection within the anterios that brings us that last move you need

SELECT tb_emp.Id AS idEmp, 
 tb_emp.Nombre AS NombreEmpleado, (select tblmovimientos.Id from 
  bd_secjo.tblmovimientos  where tb_emp.id=tblmovimientos.id)
 from bd_secjo.tblempleados as tb_emp

And so one more select within the first selec for each field you need