php - MySQL复杂连接

我有两张桌子（车辆，可选颜色）如下，
车辆

id  vehicle_name
--  ------------
1   Honda Dio
2   Yamaha FZ
3   RE Classic 350

可用颜色

id  vehicle_id  color
--  ----------  ----
1   1           Red
2   1           Yello
3   1           White
4   1           Black
5   2           Metalic Red
6   2           Metalic Black
7   2           Metalic Blue
8   3           Classic Red
9   3           Classic Black
10  3           Classic Silver

我想做一个有以下标准的手术

IF available_colors.color LIKE '%Metalic Red%'

THEN return that "vehicle" with all the "available colors",

如下所示，

id  vehicle_name    color
--  ------------    -----
2   Yamaha FZ       Metalic Red
2   Yamaha FZ       Metalic Black
2   Yamaha FZ       Metalic Blue

最佳答案

SELECT * FROM vehicles V1
JOIN  available_colors AC1 ON V1.id = AC1.vehicle_id
WHERE EXISTS
( SELECT * FROM available_colors ac
where ac.vehicle_id = v.id and ac.color like '%Metalic Red%')