我有一个查询（部分有效），如下所示：
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绑定）。我知道问题出在我的 WHERE 语句中，我在其中特别说m.vehicle_id=i.vehicle_id AND m.type='exterior'但是，它会导致忽略没有图像的条目。我需要的是一个查询，如果有结果要cms_dealer_inventory_media，它使m.vehicle_id=i.vehicle_id运行。
你应该使用left join
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
如果将查询更改为使用更现代的显式joins，则可以使用 left join s：
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
LEFT JOIN `cms_dealer_account` AS `a` ON a.user_id=i.user_id
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'
AND a.inventory_status='1'
AND m.type='exterior'
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的所有记录
所以加入将cms_dealer_inventory左外cms_dealer_inventory_media