title: Pagination and INNER JOIN
body: <p>I have a situation which I need to do pagination along with <code>INNET JOIN</code>. Here is a similar scenario I have:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>These are the records I have. As you can see, some categories do not have any products inside <code>@fooTable</code>. As a next step, we have the following <code>SELECT</code> statement:</p>
  
  <pre><code>SELECT * FROM @fooTable ft
  INNER JOIN (
      SELECT ROW_NUMBER() OVER (ORDER BY CatID) AS RowNum, * FROM @categories
  ) AS cat ON (cat.CatID = ft.CatID);
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>When you look at the <code>RowNum</code> 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:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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?</p>
  
  <p><strong>Edit</strong></p>
  
  <p>According to the above result of my first select query, I should be able to retrieve products whose <code>CatID</code> is 1 and 4 on my second query. That's what I aim.</p>