Proper pagination in a JOIN select

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

The relationship is 1-N: user may have many files.

This effectively selects the second 10-element page.

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

How to? I target PostgreSQL and HSQLDB

Answer 1

You need to limit the select on the outer table first and then join the dependent table to the result.

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