mysql limiting join

Question

I've done a few searches on this subject but non of the solutions seem to work so perhaps my requirement is slightly different.

Basically I have a "content" table and a "file_screenshots" table. Each row in the "file_screenshots" table has a "screenshot_content_id" column. I want to select from the "content" table, join the "file_screenshots" table but only select a maximum of 5 screenshots for any single piece of content.

If this isn't possible i'm happy to use two queries, but again i'm not sure how to limit the results to only receiving 5 screenshots per piece of content.

Here is an example query:

SELECT * FROM content 
LEFT JOIN file_screenshots 
ON file_screenshots.screenshot_content_id = content.content_id 
WHERE content_type_id = 4

Answer 1

Assuming you have some sort of unique id column in your file_screenshots table, this should work for you:

SELECT
    c.*,
    fs.*
FROM
    content c
JOIN
    file_screenshots fs
    ON (fs.screenshot_content_id = c.content_id)
LEFT JOIN
    file_screenshots fs2
    ON (fs2.screenshot_content_id = c.content_id AND fs2.id < fs.id)
GROUP BY
    fs.id
HAVING
    COUNT(*) < 5
ORDER BY c.content_id, fs.id

I've named the id column id. Rename it if neccessary.

If you want the 5 screenshots with the highest id, reverse the fs2.id vs. fs.id comparison.

    ON (fs2.screenshot_content_id = c.content_id AND fs2.id > fs.id)