SQL - Most efficient way to perform this query?

Question

Hi, I basically have three tables, posts, images and postimages (this simply contains the ids of both the other tables and allows more than one image in each post and each image to be reused in multiple posts if necessary).

I'm trying to list the titles of all the posts, along with a single, small image from each (if one exists)

I can do it with the following:

    $sql ="SELECT * FROM posts p, images im, postimages pi WHERE
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id
    ORDER BY created_at LIMIT 10";

But obviously, if there is more than one image, the record is displayed twice.

I know I could probably do it with a subquery, but that seems horribly inefficient. Is there a more elegant way around this?

Any advice would be appreciated. Thanks.

Answer 1

select
    *
from
    posts p
    left outer join (select post_id, max(image_id) as image_id 
        from postimages group by post_id) pi on
        p.id = pi.post_id
    left outer join images im on
        pi.image_id = im.image_id

You can do the subquery so that it only has to be executed once, not per row. This way, it's used as a subquery for an entire table, and then joined to the posts. Obviously, this can be MIN(image_id) if you want to take the first image. Really, whatever you prefer.

Edit: Made it be a left outer join to capture even those images that don't exist in the posts. This will return null for the image in the case that it happens to be non-existant.

Answer 2

SELECT p.id,p.title,im.src
FROM posts p
LEFT JOIN postimages pi
ON p.id = pi.post_id 
LEFT JOIN images im
ON pi.image_id = im.image_id
GROUP BY p.id
ORDER BY created_at 
LIMIT 10

Not sure if this is the most efficient, you will have to run this against a inner query. It will work only for MySql as far as I know.