Selecting a car listing with its images in PHP and MySQL-CodePudding

I have this query that selects a CAR together with it IMAGES from a MySQL database.

SELECT car.*, comments.*, person.*, body_type.* FROM car
INNER JOIN person
ON person.car_id = car.car_id 
INNER JOIN comments
ON comments.car_id = car.car_id 
INNER JOIN body_type
ON body_type.bodytype_id = car.bodytype_id
INNER JOIN images
ON images.car_id = car.car_id
ORDER BY added_on DESC

However, this actually counts the images from the IMAGES and returns one listing as many times as there are images from the table. For instance, if there are five images from the IMAGES table, it will return one CAR Listing Five times, when I actually want display the five images and associate them with one CAR using the CAR ID. I noticed that the problem was in the query because when I removed INNER JOIN images ON images.car_id = car.car_id, the Cars are displayed just the right way except, without the images.

CodePudding user response：

Do you wand to get the number of images for each car? Maybe this will help you (untested):

SELECT car.*, comments.*, person.*, body_type.*, count(*) as countImages
FROM car
INNER JOIN person
ON person.car_id = car.car_id 
INNER JOIN comments
ON comments.car_id = car.car_id 
INNER JOIN body_type
ON body_type.bodytype_id = car.bodytype_id
INNER JOIN images
ON images.car_id = car.car_id
GROUP BY car.car_id
ORDER BY added_on DESC