Correct pagination in participant selection

Question

I have a SQL statement

select * 
from users u left join files f
on u.id = f.user_id
where f.mime_type = 'jpg'
order by u.join_date desc
limit 10 offset 10

1-N Ratio: A user can have many files.

This effectively selects the second 10-element page.

The problem is that this query limits / cancels the joined table, but I want to limit / offset different rows from the first table ( users).

How? I am targeting PostgreSQL and HSQLDB

+5

Queequeg Apr 9 '13 at 8:34

1 answer

Udo Klein · Accepted Answer · 2013-04-09T08:45:36+0000

First you need to limit the selection in the external table and then join the dependent table to the result.

select * from (select * from users limit 10 offset 10)  as u
left join files f
   on u.id = f.user_id