php - 编写连接 4 个表的 mysql 查询时遇到问题

Question

我有一个查询(部分有效)，如下所示:

SELECT i.vehicle_id, i.user_id, i.make, i.model, i.year, i.state, i.price, i.class, i.fuel, i.mileage, i.mileage_type, i.featured, i.published, i.creation, i.status, m.vehicle_id, m.type, m.src, a.user_id, a.inventory_status, a.country, s.user_id, s.currency_iso, s.currency_code 
FROM `cms_dealer_inventory` AS `i`, 
`cms_dealer_inventory_media` AS `m`, 
`cms_dealer_account` AS `a`, 
`cms_dealer_setting` AS `s` 
WHERE i.make='chevrolet' 
AND i.model='camaro' 
AND i.year='2014' 
AND (i.mileage <= 200000 ) 
AND i.published='1' 
AND a.inventory_status='1' 
AND m.vehicle_id=i.vehicle_id 
AND m.type='exterior' 
AND a.user_id=i.user_id 
AND s.user_id=i.user_id 
AND a.country='DE' 
GROUP BY i.vehicle_id 
ORDER BY i.featured DESC, i.price ASC, i.state DESC, i.make ASC

问题是，cms_dealer_inventory_media 可能包含也可能不包含图像(通过vehicle_id 绑定(bind))。我知道问题出在我的 WHERE 语句中，我在其中明确表示 m.vehicle_id=i.vehicle_id AND m.type='exterior' 但是，它会导致没有图像的条目被忽略。我需要的是一个查询，如果 cms_dealer_inventory_media 有结果，则使 m.vehicle_id=i.vehicle_id 运行。

Answer 1

您应该使用左连接

SELECT i.vehicle_id, i.user_id, i.make, i.model, i.year, i.state, i.price, i.class, i.fuel, i.mileage, i.mileage_type, i.featured, i.published, i.creation, i.status, m.vehicle_id, m.type, m.src, a.user_id, a.inventory_status, a.country, s.user_id, s.currency_iso, s.currency_code 
FROM `cms_dealer_inventory` AS `i` left join
`cms_dealer_inventory_media` AS `m` on m.vehicle_id=i.vehicle_id 
AND m.type='exterior' left join
`cms_dealer_account` AS `a` on a.inventory_status='1' 
AND a.user_id=i.user_id AND a.country='DE' left join
`cms_dealer_setting` AS `s` ON s.user_id=i.user_id  

WHERE i.make='chevrolet' 
AND i.model='camaro' 
AND i.year='2014' 
AND (i.mileage <= 200000 ) 
AND i.published='1' 
GROUP BY i.vehicle_id 
ORDER BY i.featured DESC, i.price ASC, i.state DESC, i.make ASC