I'm trying to get data from three tables (photos, albums, album_photos), then the program searches a user's albums in the album table, then look for every album the ID's of the photos in album_photos, and then, for each ID, look at the photos table all data by ID.
Yesterday I asked something like this: Inner join with 3 tables, but now, I think the question is different, I'm wondering how I can add a limit to a request by inner join.
So, I'm working now in this code:
SELECT a.album_name, a.album_id, c.*
FROM albums a
INNER JOIN album_photos b ON a.album_id = b.album_id
INNER JOIN photos c ON b.photo_id = c.photo_id
WHERE (
SELECT COUNT(*)
FROM album_photos d
WHERE b.album_id = d.album_id
AND d.nick = :nick
) <=5
Ok, this code select's the albums that have 5 or less photos. I do not want the code to do that, no matter how many photos have the album, I want to show the album with a LIMIT OF 5 photos.
Other people have told me that you can not do it, I believe that this is not so, because the SQL language is very complex and I think we should have the tool to do it.
Is there any way to do this in a proper way?
*In the link that I'm shared above I put an example about the output data.
Try changing the where clause to this:
WHERE (
SELECT COUNT(*)
FROM album_photos d
WHERE d.album_id = b.album_id and
d.photo_id <= b.photo_id
AND d.nick = :nick
) <= 5
This counts the number of photos in order, not just the number of photos in the album.