How to select multiple rows based on another selected rows

Question

I have two tables (cars and car_images)

cars
----
id
brand
model
...

car_images
----------
id
car_id
name
...

Every car can have multiple images ...

What is the best way to select all cars with their images ?

That's how I do it with Native PHP PDO, but I don't know if it's the best and fastest way ...

$sth = $dbh->query("SELECT * FROM cars");

$sth_images = $dbh->prepare("SELECT * FROM car_images WHERE car_id = :car_id");

while($row = $sth->fetch()){
    $sth_images->execute(['car_id' => $row['id']]);
    // ...
}

use LEFT JOIN instead

teeyo
– teeyo

2017-03-10 14:55:44 +00:00
Commented Mar 10, 2017 at 14:55 — teeyo
– teeyo, Commented Mar 10, 2017 at 14:55

Yolo · Accepted Answer · 2017-03-10 14:59:06Z

3

You can do LEFT JOIN so you get cars also if they dont have images

SELECT * FROM cars LEFT JOIN car_images ON cars.id = car_images.car_id WHERE cars.id = :car_id

UPDATE

If you need to work with either car id or car image id, above will not have the desired result. This would be better:

SELECT car_images.car_id, car_images.id AS image_id, car_images.name FROM cars LEFT JOIN car_images ON cars.id = car_images.car_id WHERE cars.id = :car_id

Corrected that again :)

edited Mar 10, 2017 at 14:59

answered Mar 10, 2017 at 14:52

Yolo

1,5791 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Az.Youness Over a year ago

But this query will results with a lot of repeated data !!

Yolo Over a year ago

The first query would also return unuseful data with an overridden id. The second query would return a list of car id, image id and image name based on the car id you pass to PDO.

Collectives™ on Stack Overflow

How to select multiple rows based on another selected rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related