Home / Questions / 302760
Accepted
Bernardo Harreguy
Asked: 2020-10-23 08:11:15 +0800 CST 2020-10-23 08:11:15 +0800 CST

Limit result of a MYSQL query with INNER JOIN

  • 772

I have a complex MYSQL query with INNER JOIN and what I need is to limit the result by one column. Basically I want to know the employees who are commission agents and when was the last date they were registered. In the movement of employees, a movement is written that is called "registration of commission agent" when it is registered, but it may happen more than once, and here is the problem. Step raise the tables that you use.

Employees table

CREATE TABLE `empleados` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(50) NOT NULL,
    `apellido` TEXT NOT NULL,
    `telefono` TEXT NOT NULL,
    `comisionista` TINYINT(1) NOT NULL DEFAULT 0,
    `contrasena` TEXT NOT NULL,
    `eliminado` TINYINT(1) NOT NULL DEFAULT 0,
    `domicilio` TEXT NOT NULL,
    `correoelectronico` TEXT NOT NULL,
    `facebook` TEXT NOT NULL,
    `ganancia` DECIMAL(10,2) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    FULLTEXT INDEX `nombre` (`nombre`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

Employee movement table

CREATE TABLE `movimientosempleados` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `fecha` DATETIME NULL DEFAULT NULL,
    `tipodemovimiento` TEXT NOT NULL,
    `numero` INT(11) NOT NULL DEFAULT 0,
    `empleado` INT(11) NOT NULL DEFAULT 0,
    `datonuevo` DECIMAL(10,2) NULL DEFAULT NULL,
    `datoanterior` DECIMAL(10,2) NULL DEFAULT NULL,
    `nombrenuevo` TEXT NULL DEFAULT NULL,
    `nombreanterior` TEXT NULL DEFAULT NULL,
    `usuarioqueedita` INT(11) NULL DEFAULT NULL,
    `host` TEXT NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1757
;

The query in question is:

SELECT DISTINCT 
empleados.id, 
nombre, 
apellido, 
comisionista, 
ganancia, 
movimientosempleados.fecha 
FROM empleados 
INNER JOIN (movimientosempleados) ON (movimientosempleados.empleado = empleados.id)  
WHERE eliminado = 0  AND comisionista = 1 AND tipodemovimiento = 'ALTA COMISIONISTA';

The result I get is the following:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
  <head>
    <title>TablaDesconocida</title>
    <meta name="GENERATOR" content="HeidiSQL 10.2.0.5683">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <style type="text/css">
      thead tr {background-color: ActiveCaption; color: CaptionText;}
      th, td {vertical-align: top; font-family: "Segoe UI", Arial, Helvetica, sans-serif; font-size: 9pt; padding: 3px; }
      table, td {border: 1px solid silver;}
      table {border-collapse: collapse;}
      thead .col0 {width: 47px;}
      .col0 {text-align: right;}
      thead .col1 {width: 128px;}
      thead .col2 {width: 70px;}
      thead .col3 {width: 95px;}
      .col3 {text-align: right;}
      thead .col4 {width: 76px;}
      .col4 {text-align: right;}
      thead .col5 {width: 138px;}
    </style>
  </head>

  <body>

    <table caption="TablaDesconocida (5 rows)">
      <thead>
        <tr>
          <th class="col0">id</th>
          <th class="col1">nombre</th>
          <th class="col2">apellido</th>
          <th class="col3">comisionista</th>
          <th class="col4">ganancia</th>
          <th class="col5">fecha</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td class="col0">-1</td>
          <td class="col1">ADMINISTRADOR</td>
          <td class="col2"></td>
          <td class="col3">1</td>
          <td class="col4">9,00</td>
          <td class="col5">2019-10-11 15:12:16</td>
        </tr>
        <tr>
          <td class="col0">-1</td>
          <td class="col1">ADMINISTRADOR</td>
          <td class="col2"></td>
          <td class="col3">1</td>
          <td class="col4">9,00</td>
          <td class="col5">2019-10-11 15:19:25</td>
        </tr>
        <tr>
          <td class="col0">0</td>
          <td class="col1">USUARIO</td>
          <td class="col2">DF</td>
          <td class="col3">1</td>
          <td class="col4">900,00</td>
          <td class="col5">2019-10-11 15:21:20</td>
        </tr>
        <tr>
          <td class="col0">3</td>
          <td class="col1">BERNARDO</td>
          <td class="col2"></td>
          <td class="col3">1</td>
          <td class="col4">12,00</td>
          <td class="col5">2019-10-11 14:35:19</td>
        </tr>
        <tr>
          <td class="col0">3</td>
          <td class="col1">BERNARDO</td>
          <td class="col2"></td>
          <td class="col3">1</td>
          <td class="col4">12,00</td>
          <td class="col5">2019-10-11 16:17:58</td>
        </tr>
      </tbody>
    </table>


  </body>
</html>

What I need would be, that the results be limited for each employee, that is to say that the employees are not repeated because they are repeated because there are several times that the 'HIGH COMMISSION AGENT' movement is carried out and also I only need to obtain the last date of "high commission agent " which would be the DATETIME of DATE in movimientosempleadosthe previous ones are not necessary! I hope someone can help me, Thanks.

mysql

1 Answers

  1. Best Answer

    You need to do a MAX in "movimientostrabajos.fecha" to give you the last date you are looking for and a GROUP BY to group them by employee

    SELECT DISTINCT 
empleados.id, 
nombre, 
apellido, 
comisionista, 
ganancia, 
MAX(movimientosempleados.fecha) 
FROM empleados 
INNER JOIN (movimientosempleados) ON (movimientosempleados.empleado = empleados.id)  
WHERE eliminado = 0  AND comisionista = 1 AND tipodemovimiento = 'ALTA COMISIONISTA' GROUP BY empleados.id;
    • 1