CREATE TABLE `vw_newcars` (
`id` INT NOT NULL AUTO_INCREMENT,
`model_id` INT NOT NULL,
`offer_table` VARCHAR(32) NOT NULL,
`make_name` VARCHAR(32) NOT NULL,
`offer_order` INT NOT NULL,
`model_name` VARCHAR(64) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_offer_make_order` (`offer_table`,`make_name`,`offer_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `image` (
`id` INT NOT NULL AUTO_INCREMENT,
`image_table` VARCHAR(32) NOT NULL,
`image_table_id` INT NOT NULL,
`image_path` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_img_table_id` (`image_table`,`image_table_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `vw_newcars` (`model_id`,`offer_table`,`make_name`,`offer_order`,`model_name`) VALUES
( 101, 'derivative', 'Fiat', 3, 'Fiat Panda' ),
( 102, 'derivative', 'Fiat', 12, 'Fiat 500' ),
( 103, 'derivative', 'Fiat', 9, 'Fiat Tipo' ),
( 104, 'derivative', 'Fiat', 4, 'Fiat Doblo' ),
( 105, 'derivative', 'Fiat', 15, 'Fiat Punto' ),
( 106, 'derivative', 'Fiat', 8, 'Fiat 124 Spider' ),
( 107, 'derivative', 'Fiat', 7, 'Fiat Freemont' ),
( 108, 'derivative', 'Fiat', 6, 'Fiat Bravo' ),
( 109, 'derivative', 'Fiat', 1, 'Fiat 500L' ),
( 110, 'derivative', 'Fiat', 10, 'Fiat 500X' ),
( 111, 'derivative', 'Fiat', 11, 'Fiat Linea' ),
( 112, 'derivative', 'Fiat', 14, 'Fiat Sedici' ),
( 113, 'derivative', 'Fiat', 13, 'Fiat Qubo' ),
( 114, 'derivative', 'Fiat', 2, 'Fiat 600' ),
( 115, 'derivative', 'Fiat', 5, 'Fiat Multipla' ),
( 201, 'derivative', 'Toyota', 1, 'Toyota Corolla' ),
( 202, 'derivative', 'Toyota', 2, 'Toyota Camry' ),
( 203, 'derivative', 'Toyota', 3, 'Toyota Yaris' ),
( 204, 'derivative', 'Toyota', 4, 'Toyota RAV4' ),
( 205, 'derivative', 'Toyota', 5, 'Toyota Supra' );
INSERT INTO `image` (`image_table`,`image_table_id`,`image_path`) VALUES
('model', 101, '/images/fiat/panda.jpg'),
('model', 102, '/images/fiat/500.jpg'),
('model', 105, '/images/fiat/punto.jpg'),
('model', 110, '/images/fiat/500x.jpg'),
('model', 115, '/images/fiat/multipla.jpg'),
('model', 201, '/images/toyota/corolla.jpg'),
('model', 202, '/images/toyota/camry.jpg');
SELECT `new_car`.*, `image_path` `model_img`
FROM `vw_newcars` `new_car`
LEFT JOIN `image`
ON `image`.`image_table_id` = `new_car`.`model_id`
AND `image_table` = 'model'
WHERE `offer_table` = 'derivative'
AND `make_name` = 'Fiat'
ORDER BY `offer_order`
LIMIT 12
Telegram: DB developers channel