Limit join with pagination-CodePudding

These are my db tables:

Users

| id | nme  |
|----|------|
| 1  | Adam |
| 2  | Bob  |
| 3  | Jan  |
| 4  | Nico |

Products

| id | price |
|----|-------|
| 1  | 500   |
| 2  | 700   |
| 3  | 900   |

Orders

| id | user_id | product_id |
|----|---------|------------|
| 1  | 1       | 1          |
| 2  | 1       | 2          |
| 3  | 1       | 3          |
| 7  | 3       | 1          |
| 8  | 3       | 2          |
| 9  | 3       | 3          |
| 10 | 4       | 3          |

I want to get up to 2 users, and their products bought. I came with this:

SELECT 
    users.id AS 'user_id', products.id AS 'product_id'
FROM 
    users
INNER JOIN 
    orders ON orders.user_id = users.id
INNER JOIN 
    products ON products.id = orders.product_id
ORDER BY 
    orders.id
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

But this returns:

| user_id | product_id |
|---------|------------|
| 1       | 1          |
| 1       | 2          |

What I want to get is up to 2 users, not orders. I want to get this:

| user_id | product_id |
|---------|------------|
| 1       | 1          |
| 1       | 2          |
| 1       | 3          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |

Any ideas?

CodePudding user response：

I think you don't need a JOIN among tables but only using [Orders] table along with a window function such as DENSE_RANK() as seems the most suitable to filter out to return the result set such as

;WITH cte AS
(
 SELECT *, 
        DENSE_RANK() OVER (ORDER BY [user_id]) AS rn
   FROM [Orders]
)
SELECT [user_id], [product_id]
  FROM cte
 WHERE rn <= 2 -- returns only two user after sorted by their ids
 ORDER BY 1, 2

Demo

CodePudding user response：

You may use DENSE_RANK function with your join query as the following:

SELECT user_id, product_id
FROM
(
  SELECT U.id AS 'user_id', 
       P.id AS 'product_id',
       DENSE_RANK() OVER (ORDER BY U.id) dr
  FROM users U INNER JOIN orders O
  ON O.user_id = U.id
  INNER JOIN products P
  ON P.id = O.product_id
) T
WHERE dr between 1 and 2
ORDER BY user_id, product_id

WHERE dr between 1 and 2 specifies the number of users you want to get according to their ids, i.e if you want to get the first three users, it will be WHERE dr between 1 and 3.

Check this demo.