How do I get a count of associated rows in a left join in MySQL?

Question

I have two tables, a vehicle table with columns:

• id
• stock
• year
• make
• model

and an images table with columns:

• id
• vehicle_id
• name
• caption
• default tinyint(1)

I am trying to list the vehicle's information, its default image, and a total count of images the vehicle has. Currently I am using the following SELECT statement:

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

I initially was using:

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

but then the images count would only be 1 or 0 depending if there was a default image in the database. I have tried using UNION and other SELECT statements but I am still unable to get a proper result. Do I need to use two SELECT statements or is there another way to handle it with JOIN or UNION?

Solution 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`

Solution 2

In the way the anser suggests, you get repeated values of "vehicle". A better way, is to group results. Try without the JOIN :

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`

Solution 3

Let me make it clear for everyone!

Task: Print 3 columns table:

1. Vehicles (titles) from vehicles table.
2. Amount of comments for each vehicle from comments table.
3. Amount of images for each vehicle from images table.

Expected output (just an example):

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

Solution:

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

Comments

[Jedihomer Townend]

I agree. This would be the correct way to do this. I can think of another way (involving using a subquery instead of the straight images table) but it is hacky and hideous (and just for fun). This is the right way to do this.

[TheTXI]

+1 for this. I cleaned it up just a little for getting rid of the scroll

[Cris McLaughlin]

Is it better to use COUNT(id) instead of COUNT(*) so that I am not selecting all of the columns?

[chaos]

Makes no difference in this situation. In general, COUNT(*) should be used when what you mean is "how many rows". COUNT(id) means "how many ids that aren't null", which may or may not be a much more involved question.

[pr1001]

I believe there are some performance benefits for using COUNT(*) instead of COUNT(id) when querying MyISAM tables, since I believe they store the total row count. However, this can probably only be used if you do not have any conditions in your query.

[Vitaliy Z]

This one is better than accepted answer, @giuseppe try to edit accepted answer, maybe author will change it