SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE product( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(50) NOT NULL ) ENGINE=INNODB; CREATE TABLE file( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, filename VARCHAR(50) NOT NULL ) ENGINE=INNODB; CREATE TABLE product2file( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, product_id INT NOT NULL, file_id INT NOT NULL, FOREIGN KEY (product_id) REFERENCES product(id), FOREIGN KEY (file_id) REFERENCES file(id) ) ENGINE=INNODB; INSERT INTO product(title) VALUES ('Молоко'), ('Конфеты'), ('Чай'); INSERT INTO file(filename) VALUES ('milk1.jpg'), ('milk2.jpg'), ('milk3.jpg'), ('tea.jpg'); INSERT INTO product2file(product_id, file_id) VALUES (1, 1), (1, 2), (1, 3), (3, 4); SELECT p.*, JSON_ARRAYAGG(f.filename) AS files FROM product p LEFT JOIN product2file p2f ON p2f.product_id=p.id LEFT JOIN file f ON p2f.file_id=f.id GROUP BY p.id;