Как получить количество связанных строк в левом соединении в MySQL?

У меня две таблицы, таблица vehicle со столбцами:

id
stock
year
make
model

и images таблица со столбцами:

id
vehicle_id
name
caption
default tinyint(1)

Я пытаюсь перечислить информацию о транспортном средстве, изображение по умолчанию и общее количество изображений, которое имеет транспортное средство. В настоящее время я использую следующий оператор SELECT:

SELECT vehicle.id, vehicle.stock, vehicle.year,
    vehicle.make, vehicle.model, images.name,
    COUNT(images.id)
FROM vehicle
LEFT JOIN images
ON vehicle.id = images.vehicle_id

Я изначально использовал:

ON vehicle.id = images.vehicle_id AND images.default = 1

но тогда количество изображений будет только 1 или 0 в зависимости от того, было ли изображение по умолчанию в базе данных. Я попытался использовать UNION и другие операторы SELECT, но я все еще не могу получить правильный результат. Нужно ли использовать два оператора SELECT или есть другой способ справиться с ним с помощью JOIN или UNION?

Ответ 1

SELECT 
    `vehicle`.`id`, 
    `vehicle`.`stock`, 
    `vehicle`.`year`, 
    `vehicle`.`make`, 
    `vehicle`.`model`, 
    `images`.`name`,
    (
        SELECT COUNT(*) 
        FROM `images` 
        WHERE `vehicle_id` = `vehicle`.`id`
    ) AS `image_count`
FROM `vehicle`
LEFT JOIN `images`
ON `images`.`vehicle_id` = `vehicle`.`id`
WHERE `images`.`default`

Ответ 2

В способе, которым предлагает anser, вы получаете повторяющиеся значения "автомобиль". Лучший способ - собрать результаты. Попробуйте без участия:

SELECT 
    `vehicle`.`id`, 
    `vehicle`.`stock`, 
    `vehicle`.`year`, 
    `vehicle`.`make`, 
    `vehicle`.`model`, 
    `images`.`name`,
    (
        SELECT COUNT(*) 
        FROM `images` 
        WHERE `vehicle_id` = `vehicle`.`id`
    ) AS `image_count`
FROM `vehicle`

WHERE `images`.`default`

Ответ 3

Позвольте мне дать понять всем!

Задача: печать таблицы с тремя столбцами:

Транспортные средства (заголовки) из таблицы транспортных средств.
Количество комментариев для каждого автомобиля из таблицы комментариев.
Количество изображений для каждого автомобиля из таблицы изображений.

Ожидаемый результат (только пример):

+----------------------+----------------+--------------+
|        title         | comments_count | images_count |
+----------------------+----------------+--------------+
| BMW X6               |             35 |            9 |
| Audi A6              |              3 |            5 |
| Volkswagen Passat B6 |             78 |            6 |
| Volkswagen Passat B5 |            129 |            4 |
+----------------------+----------------+--------------+

Решение

SELECT 
    vehicles.title,
    (SELECT COUNT(*) FROM comments WHERE vehicles.id = comments.vehicle_id) AS comments_count,
    (SELECT COUNT(*) FROM images WHERE vehicles.id = images.vehicle_id) AS images_count
FROM vehicles