applying paging to join queries
Hi,
I'd like to apply paging (skip the first $offset entries and provide $maxresults entries) to my query.
Investigating this brought up this sql statement:
select * from (SELECT paging.*, rownum rn FROM ([usual sql goes here]) paging where rownum <= :paging1) where rn > :paging0
Unfortunately there is now a discrepancy between the number of entries expected and the number of rows returned.
While this method gives you exactly $maxresults rows, I really want to limit the amount of returned entries of the main table.
Let's say the sql without paging looks like
select * from tree t left join leave l on t.id=l.id
I'd like to apply paging (skip the first $offset entries and provide $maxresults entries) to my query.
Investigating this brought up this sql statement:
select * from (SELECT paging.*, rownum rn FROM ([usual sql goes here]) paging where rownum <= :paging1) where rn > :paging0
Unfortunately there is now a discrepancy between the number of entries expected and the number of rows returned.
While this method gives you exactly $maxresults rows, I really want to limit the amount of returned entries of the main table.
Let's say the sql without paging looks like
select * from tree t left join leave l on t.id=l.id
0