Pagination and INNER JOIN

inner-join, sql-server, sql-server-2008-r2, t-sql

Solution

One solution can be the next one:

SELECT  x.*
FROM
(
        SELECT  ft.*, 
                cat.CategoryName,
                DENSE_RANK() OVER (ORDER BY ft.CatID) AS Rnk
        FROM @fooTable ft
        INNER JOIN @categories cat ON (cat.CatID = ft.CatID)
) AS x
WHERE   x.Rnk BETWEEN 1 AND 2

Results:

ID CatID Name     MinAllow Price   CategoryName Rnk
-- ----- -------- -------- ------- ------------ ---
1  1     Product1 2        112.20  Cat1         1
3  1     Product3 5        233.32  Cat1         1
6  1     Product6 4        12.43   Cat1         1
7  4     Product7 4        12.43   Cat4         2

Problem

I have a situation which I need to do pagination along with `INNET JOIN`. Here is a similar scenario I have: ``` DECLARE @categories AS TABLE( CatID INT, CategoryName NVARCHAR(100) ); DECLARE @fooTable AS TABLE( ID INT, CatID INT, Name NVARCHAR(100), MinAllow INT, Price DECIMAL(18,2) ); INSERT INTO @categories VALUES(1, 'Cat1'); INSERT INTO @categories VALUES(2, 'Cat2'); INSERT INTO @categories VALUES(3, 'Cat3'); INSERT INTO @categories VALUES(4, 'Cat4'); INSERT INTO @categories VALUES(5, 'Cat5'); INSERT INTO @fooTable VALUES(1, 1, 'Product1', 2, 112.2); INSERT INTO @fooTable VALUES(3, 1, 'Product3', 5, 233.32); INSERT INTO @fooTable VALUES(6, 1, 'Product6', 4, 12.43); INSERT INTO @fooTable VALUES(7, 4, 'Product7', 4, 12.43); INSERT INTO @fooTable VALUES(8, 5, 'Product8', 4, 12.43); ``` These are the records I have. As you can see, some categories do not have any products inside `@fooTable`. As a next step, we have the following `SELECT` statement: ``` SELECT * FROM @fooTable ft INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY CatID) AS RowNum, * FROM @categories ) AS cat ON (cat.CatID = ft.CatID); ``` It is a basic JOIN except that the output will also carry the row number of the categories. The result I got for this query is as follows: ``` ID CatID Name MinAllow Price RowNum CatID CategoryName ---- ------- ------------- ----------- --------- -------- -------- ------------- 1 1 Product1 2 112.20 1 1 Cat1 3 1 Product3 5 233.32 1 1 Cat1 6 1 Product6 4 12.43 1 1 Cat1 7 4 Product7 4 12.43 4 4 Cat4 8 5 Product8 4 12.43 5 5 Cat5 ``` When you look at the `RowNum` column, you will see that those values are not pagination friendly. So, when I try to paginate this table as follows, I got an incorrect output: ``` SELECT * FROM @fooTable ft INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY CatID) AS RowNum, * FROM @categories )AS cat ON (cat.CatID = ft.CatID) AND (cat.RowNum BETWEEN 1 AND 2); ``` The real situation I have is similar to this one but that query is so complicated and I need to get it working with INNER JOIN. I hope I made it clear. Any idea how I got something like that working? Edit According to the above result of my first select query, I should be able to retrieve products whose `CatID` is 1 and 4 on my second query. That's what I aim.

Original source