drop table IF EXISTS imagenes;
drop table IF EXISTS caracteristicas;
Create table caracteristicas (
id Int UNSIGNED NOT NULL AUTO_INCREMENT,
texto Varchar(100),
Index AI_id (id),
Primary Key (id)
) ENGINE = InnoDB;
Create table imagenes (
id Int UNSIGNED NOT NULL AUTO_INCREMENT,
ruta_imagen Varchar(100) NOT NULL,
caracteristicas_id Int UNSIGNED NOT NULL,
Index AI_id (id),
Primary Key (id),
Constraint R001 Foreign Key (caracteristicas_id) references caracteristicas (id) on delete restrict on update cascade
) ENGINE = InnoDB;
INSERT INTO caracteristicas(texto) VALUES ('Hola mundo');INSERT INTO caracteristicas(texto) VALUES ('Comida rapida');INSERT INTO caracteristicas(texto) VALUES ('Politica');
INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_uno.jpg',1);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_dos.jpg',1);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_tres.jpg',1);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_cuatro.jpg',2);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_cinco.jpg',2);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_seis.jpg',2);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_siete.jpg',3);INSERT INTO imagenes(ruta_imagen, caracteristicas_id) VALUES('imagen/imagen_ocho.jpg',3);
SELECT c.*,
(SELECT i.ruta_imagen
FROM imagenes AS i
WHERE i.caracteristicas_id=c.id
ORDER BY i.id
LIMIT 1) AS imagen
FROM caracteristicas AS c;
