SQL Server SELECT paging with JOIN

Question

I am selecting from a table and doing a left join with a many to one relationship.

My problem to solve is with paging. I want to do paging on tableA only.

Example:

SELECT * 
FROM tableA 
[WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

This will return rows 0 - 9 of tableA. Perfect.

Now the problem is when I introduce the join. I still want tableA's rows 1-10 but when I do the join it introduces extra rows as expected since tableB will have multiple entries to join against each tableA row. So now I no longer get the same rows from tableA, I may only get the first 2 rows but have 10 total because of the join.

SELECT * 
FROM tableA 
LEFT JOIN tableB ON foo = bar 
[WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

I want to get back as many rows as the join produces but only on tableA's 0-9 (or 10-19)

To clarify, there are multiple tableB rows for each tableA row. I want to select and page based on tableA only but still get back an entry for all the joins on tableB.

Answer 1

You can use inner query in this case

SELECT *
   FROM (
   SELECT * FROM tableA [WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
   ) q
JOIN tableB on foo = bar [WHERE HERE]