Inner join gives undesired result with row number in sql server

Question

I've tried to do a pagination on data in using ROW_NUMBER()
Here is my query:

SELECT * FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS Row,* FROM SpecificOrders) 
    AS EMP
        inner join Users as c on EMP.UserID = c.UserID
        inner join Users as u on EMP.CreatedBy = u.UserID       
        inner join SpecificOrderPayment as p on EMP.OrderID= p.OrderID

    WHERE Row BETWEEN 0 AND 10

When I execute this query, I get output like following with :

Row | OrderID | UserID |
1   |         |        |
5   |         |        |
6   |         |        |
7   |         |        |
8   |         |        |
9   |         |        |
10  |         |        |

If I remove this WHERE Row BETWEEN 0 AND 10 condition then it'll gives me all records

Here my question is why I get only 7 rows and why here 2,3 and 4 is missing in the row column.

Moreover, If i remove 3rd join query (SpecificOrderPayment)then it will give me proper result.

There seems to be no matching records in `SpecificOrderPayment` for the cases that you mention. Try with `LEFT` instead of `INNER` join. — Giorgos Betsos, Sep 14 '16 at 10:09
How to use row number function?? visit this link http://stackoverflow.com/questions/961007/how-do-i-use-row-number — Mukesh Kalgude, Sep 14 '16 at 10:10
@MukeshKalgude *How to use row number function?* No, the question is different. — Divyang Desai, Sep 14 '16 at 10:12
Because, your row 2 doesn't match with inner join condition, so the line is cut — Joe Taras, Sep 14 '16 at 10:15
@Div your question is different but your concept is wrong so refer this link — Mukesh Kalgude, Sep 14 '16 at 10:16
Why you don't just use SELECT TOP 10 * FROM SpecificOrders ... JOIN.... — PyQL, Sep 14 '16 at 10:19
@MukeshKalgude, *your concept is wrong* How? BTW thanks for a link — Divyang Desai, Sep 14 '16 at 10:20
@PyQL Why I use that? here in the question it's static, but it will use dynamically from code for pagination purpose. — Divyang Desai, Sep 14 '16 at 10:24
Also I have to point here that if you are using SQL Server 2012 and up, you could use OFFSET/FETCH to implement the pagination. — S.Karras, Sep 14 '16 at 10:25
Inner joins are filtering out the result. Run the sub query alone and see the output. Then see how it behaves with the joins. Also see reply from @MtwStark. — The Shooter, Sep 14 '16 at 11:24

score 2 · Accepted Answer · answered Sep 14 '16 at 10:32

2

you've got OrderID that are null or blanks in SpecificOrders and they are sorting to the top - the approach isn't wrong otherwise, although there are other ways of doing it such as TOP 10..etc

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS Row,* FROM SpecificOrders 
                                                  WHERE RTRIM(COALESCE(OrderID, '')) <> '') 
AS EMP
    inner join Users as c on EMP.UserID = c.UserID
    inner join Users as u on EMP.CreatedBy = u.UserID       
    inner join SpecificOrderPayment as p on EMP.OrderID= p.OrderID

WHERE Row BETWEEN 0 AND 10

answered Sep 14 '16 at 10:32

Cato

3,652
9
12

It's worked, but what if i have one an other table called `SpecificOrderDetails` and which contains multiple record with single orderID, how can use this? – Divyang Desai Sep 14 '16 at 10:52
1

@div you could join using OUTER APPLY and join to only the TOP 1 - I'm not sure of what you want to achieve though, what info do you want from SpecificOrderDetails ? – Cato Sep 14 '16 at 10:55
I don't know the structure of tables, but I thought that OrderId was something like a not nullable (primary?) key in table SpecificOrders, in this case, the problem is not there but in other joined table, the most probably is SpecificOrderPayment because (I think) payment could be not mandatory for the order, while user should be. the last inner join could be dangerous.. – MtwStark Sep 30 '16 at 08:23
@div can you pleas run this query? `select count(*) from SpecificOrders where OrderID is null` and post result? – MtwStark Sep 30 '16 at 08:29
@div no problem, I was just wondering about the solution of Andrew. He thinks the problem is that SpecificOrders.OrderID can be NULL, so he suggests to filter it before to INNER join other tables. I think that the problem is the missing of a related SpecificOrderPayment record or (less probably) nulls in SpecificOrders.UserID and SpecificOrders.CreatedBy columns, but In every case the inner joins will filter out from result all orders without a payment or with null userid and createdby so, be sure to check for it. – MtwStark Sep 30 '16 at 08:49
@div I don't think a left join can give duplicate results where the inner join does not. if the join condition is matched left/inner gives same result, if not matched left keeps the left record while inner discard it, but no duplicates are generated but, I'm happy you have found the solution good for you.... – MtwStark Sep 30 '16 at 09:25

MtwStark · Answer 2 · 2016-09-14T12:42:41.410

2

The problem is that you are numbering the rows of SpecificOrders and not of final result.

In your case you only have one row per order, so using left joins should solve the issue

But, if the inner query could return multiple rows for each OrderID you will see the same row number many times

And if the join (inner) will filter some row you will not get that row number in result.

You have simply to separate the query for data extraction from the query for pagination,

Try this:

SELECT * 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY EMP.OrderID) AS Row, EMP.*
    FROM SpecificOrders AS EMP
    left join Users as c on EMP.UserID = c.UserID
    left join Users as u on EMP.CreatedBy = u.UserID       
    left join SpecificOrderPayment as p on EMP.OrderID= p.OrderID
) D
WHERE [Row] BETWEEN 0 AND 10

edited Sep 14 '16 at 12:42

answered Sep 14 '16 at 11:13

MtwStark

3,866
1
18
32

the error is because with `SELECT * FROM` you get same column name many times, I will correct the `SELECT` to avoid the issue – MtwStark Sep 14 '16 at 12:39
glad to be of any help. after `EMP.*` add the columns you need from the tables, and then, if you like this solution, please accept it as best answer – MtwStark Sep 14 '16 at 13:11

Inner join gives undesired result with row number in sql server

2 Answers2