MYSQL LEFT JOIN con filtros de más: https://es.stackoverflow.com/q/103125/29967, MySQL

Language: Layout:

/*
    -- ATENCIÓN NO USE DROP TABLE CON SUS TABLAS REALES
    -- YA QUE DROP TABLE BORRARÁ SUS DATOS
    -- DROP TABLE SE USA AQUÍ SÓLO PARA PODER PROBAR LOS DATOS 
    */

SET FOREIGN_KEY_CHECKS=0; 
    DROP TABLE IF EXISTS varieties_20170918;
    DROP TABLE IF EXISTS units_20170918;
    DROP TABLE IF EXISTS sowing_20170918;

SET FOREIGN_KEY_CHECKS=1;

/* NO COPIE LA SENTENCIA ^ DROP TABLE ^ EN SUS DATOS REALES */

CREATE TABLE IF NOT EXISTS varieties_20170918 
    (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        variety VARCHAR(3),
        status INT
    )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS units_20170918 
    (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        id_grouper INT,
        unit INT,
        status INT
    )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS sowing_20170918 (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        id_grouper INT, 
        id_unit INT,
        id_variety INT,
        type VARCHAR(2),
        status INT,
        FOREIGN KEY (id_unit) REFERENCES units_20170918(id)
        ON DELETE CASCADE,
        FOREIGN KEY (id_variety) REFERENCES varieties_20170918(id)
        ON DELETE CASCADE
    )ENGINE=INNODB;

INSERT INTO varieties_20170918 (variety,status)
        VALUES 
        ('VS1',1),
        ('VS2',1),
        ('VS3',1),
        ('VS4',1),
        ('VS5',1)
    ;

INSERT INTO units_20170918 (id_grouper,unit,status)
        VALUES 
        (13,1,1),
        (13,2,1),
        (13,3,1),
        (13,4,1),
        (13,5,1),
        (13,6,1),
        (13,7,1)
    ;

INSERT INTO sowing_20170918 (id_grouper,id_unit,id_variety,type,status)
        VALUES 
        (13,1,1,'SW',1),
        (13,3,1,'SW',1),
        (13,4,1,'SW',1),
        (13,6,3,'SW',1),
        (13,7,4,'ER',1)
    ;

SELECT u.unit, v.variety
    FROM       
    units_20170918 u
    LEFT JOIN sowing_20170918 s ON u.id = s.id_unit
    LEFT JOIN varieties_20170918 v ON s.id_variety = v.id
    WHERE u.id_grouper = 13 AND u.status = 1;


absolute service time: 0,39 sec

MYSQL LEFT JOIN con filtros de más: https://es.stackoverflow.com/q/103...