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

Check this question: http://stackoverflow.com/questions/1042481/mysql-join-with-limit — ypercubeᵀᴹ, Sep 22 '11 at 09:37
And this: http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category — ypercubeᵀᴹ, Sep 22 '11 at 09:39
Cheers for the links but I can't see how to directly use the examples given in those answers to my own solution. The second one returns a list of all the content with extra columns for a single screenshot (rather than 5 rows for each piece of content as I would like) and I can't figure out exactly how to rewrite the first link for my own example at all. — John Mellor, Sep 22 '11 at 09:59

Ivar Bonsaksen · Accepted Answer · 2011-09-22T10:30:28.993

2

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)

edited Sep 22 '11 at 10:30

answered Sep 22 '11 at 10:20

Ivar Bonsaksen

4,747
2
32
34

Cheers that works really well, the only problem I could foresee is that it won't work if there are no screenshots, even if you use left join. For me that's not a problem right now, but it should be noted for anyone else reading this :) – John Mellor Sep 22 '11 at 11:01
@FreezeDriedPop Replace the first `JOIN` with a `LEFT JOIN`, and you should also get rows from `content` that have no corresponding rows in `file_screenshots` – Ivar Bonsaksen Sep 22 '11 at 11:09

mysql limiting join

1 Answers1