MYSQL: Estudiantes y centros tabla asociativa - https://es.stackoverflow.com/q/185957/29967, MySQL

Language: Layout:

/*MYSQL: Estudiantes y centros tabla asociativa - https://es.stackoverflow.com/q/185957/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 student;
    DROP TABLE IF EXISTS headquarter;
    DROP TABLE IF EXISTS student_headquarter;

SET FOREIGN_KEY_CHECKS=1;

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

CREATE TABLE IF NOT EXISTS student 
    (
        student_id       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        carnet           VARCHAR(12),
        student_name     VARCHAR(50),
        CONSTRAINT student_AK01 UNIQUE (carnet)
    )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS headquarter 
    (
        hq_id         INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        headquarter   VARCHAR(150),
        CONSTRAINT headquarter_AK01 UNIQUE (headquarter)
     )ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS student_headquarter 
    (
        carnet          VARCHAR(12),
        hq_id           INT,
        type            TINYINT,
        CONSTRAINT student_headquarter_PK PRIMARY KEY (carnet,hq_id,type),
        FOREIGN KEY (carnet) REFERENCES student(carnet) 
            ON UPDATE CASCADE ON DELETE  CASCADE, 
        FOREIGN KEY (hq_id) REFERENCES headquarter(hq_id) 
            ON UPDATE CASCADE ON DELETE  CASCADE            
    )ENGINE=INNODB;

INSERT INTO student (student_name, carnet) 
        VALUES
        ('Pedro', 'P-01'),
        ('Santiago', 'S-01'),
        ('Juan', 'J-01') ;

INSERT INTO headquarter (headquarter) 
        VALUES
        ('Sede 1'),
        ('Sede 2'),
        ('Sede 3'),
        ('Sede 4'),
        ('Sede 5');

INSERT INTO student_headquarter (carnet,hq_id,type) 
        VALUES
        ('P-01',1,1),
        ('P-01',5,2),
        ('S-01',2,1),
        ('S-01',3,2),
        ('J-01',4,1),
        ('J-01',1,2),
        ('P-01',2,2);

SET sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT 
        s.carnet, 
        s.student_name, 
        h.headquarter, 
        sh.type 
    FROM student_headquarter sh
        INNER JOIN student s  ON sh.carnet=s.carnet
        INNER JOIN headquarter h ON sh.hq_id=h.hq_id;


absolute service time: 0,39 sec

MYSQL: Estudiantes y centros tabla asociativa - https://es.stackoverfl...