MYSQL: JOIN de 4 tablas con GROUP BY o DISTINCT - https://es.stackoverflow.com/q/134315/29967, MySQL

Language: Layout:

/*MYSQL: JOIN de cuatro tablas con GROUP BY o con DISTINCT - https://es.stackoverflow.com/q/134315/29967*/

/*
    -- 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 series_trailers;
    DROP TABLE IF EXISTS series_episodes;
    DROP TABLE IF EXISTS series;    
    DROP TABLE IF EXISTS authors;    
    SET FOREIGN_KEY_CHECKS=1;

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

CREATE TABLE IF NOT EXISTS series_trailers 
    (
        ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        EPISODEID INT,
        CONTENT VARCHAR(255),
        AUTHORID INT
        
    )ENGINE=INNODB;

INSERT INTO series_trailers (EPISODEID,CONTENT,AUTHORID)
        VALUES 
        (122383,'url1',  1),
        (9999,  'url2',  2),
        (923822,'stuff', 3),
        (122384,'url3',  4)
    ;

/*Puede que el ID de esta tabla sea auto-incremental*/ 
   CREATE TABLE IF NOT EXISTS series_episodes 
    (
        ID INT NOT NULL PRIMARY KEY, 
        TITLE VARCHAR(255),
        SERIESID INT
        
    )ENGINE=INNODB;

INSERT INTO series_episodes (ID,TITLE,SERIESID)
        VALUES 
        (122383,'Episode 1',23),
        (9999,  'Somethingweird',87),
        (923822,'Randomtitle',52),
        (122384,'Episode 2',23)
    ;

/*Puede que el ID de esta tabla sea auto-incremental*/ 
   CREATE TABLE IF NOT EXISTS series 
    (
        ID INT NOT NULL PRIMARY KEY, 
        TITLE VARCHAR(255)
        
    )ENGINE=INNODB;    
    
 	INSERT INTO series (ID, TITLE)
        VALUES 
        (23,'Stranger Things'),
        (87,'Seriesname'),
        (512,'Sometrashseries') 
    ;

CREATE TABLE IF NOT EXISTS authors 
    (
        ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        AUTHOR VARCHAR(255)
        
    )ENGINE=INNODB;  
    
 	INSERT INTO authors (AUTHOR)
        VALUES 
        ('Peter'),
        ('Anna'), 
        ('Jhon'),
        ('Drake') ;
           
    /*Consultas de prueba*/
       -- Con GROUP BY
    SELECT s.TITLE FROM series_episodes se
    INNER JOIN series s ON se.SERIESID = s.ID
    INNER JOIN series_trailers st ON se.ID = st.EPISODEID 
    GROUP BY s.ID
    ORDER BY se.ID DESC;

-- Con GROUP BY, incluyendo JOIN de AUTHORS
    SELECT * FROM series_episodes se
    INNER JOIN series s ON se.SERIESID = s.ID
    INNER JOIN series_trailers st ON se.ID = st.EPISODEID 
    INNER JOIN authors a ON a.ID=st.AUTHORID
    GROUP BY s.ID
    ORDER BY se.ID DESC;

-- Con DISTINCT
    SELECT DISTINCT s.TITLE FROM series_episodes se
    INNER JOIN series s ON se.SERIESID = s.ID
    INNER JOIN series_trailers st ON se.ID = st.EPISODEID
    ORDER BY se.ID DESC;


absolute service time: 0,4 sec

MYSQL: JOIN de 4 tablas con GROUP BY o DISTINCT - https://es.stackover...