CODEWITHSUNDEEP
sqlsql-serverselectjoin
Sean256
Sean256

Reputation: 3099

SQL Server SELECT paging with JOIN

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.

Upvotes: 7

Views: 3052

Answers (2)

nbirla
nbirla

Reputation: 610

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]

Upvotes: 6

AndreDuarte
AndreDuarte

Reputation: 804

I don't think it's gonna work with you query, cause this 0-10 fetch will be aplied to reults returned by the join. Did you tried to do something like this:

SELECT * FROM tableB on foo in 
    SELECT bar FROM tableA [WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Upvotes: 0

Related Questions