CODEWITHSUNDEEP
sql-server
user1050619
user1050619

Reputation: 20856

SQL Server INNER JOIN on 2 same tables

Table-1

START   END rownum
1   1   1
2   3   2
4   7   3
8   8   4
9   9   5

Table-2

START   END rownum
1   1   1
2   3   2
4   7   3
8   8   4
9   9   5

Query:

SELECT TB1.START,TB1.[END],TB1.rownum as row_1,TB2.START,TB2.[END],TB2.rownum as row_2 FROM 
(
select START,[END],ROW_NUMBER() over(order by import_num) as rownum from 
table1 
) TB1
INNER JOIN
(
select START,[END],ROW_NUMBER() over(order by import_num) as rownum from 
table1 
) TB2
ON TB1.rownum + 1 = TB2.rownum

Result:

START   END row_1   START   END row_2
1   1   1   2   3   2
2   3   2   4   7   3
4   7   3   8   8   4
8   8   4   9   9   5

Question:

Why would the rownum will indicate-1,2,3,4 when my join condition is on tb1.rownum + 1? Shouldn't it be 2,3,4,5

Upvotes: 0

Views: 146

Answers (3)

coge.soft
coge.soft

Reputation: 1674

You're JOINing on rownum + 1, but not SELECTing it...

Change: 

SELECT TB1.START,TB1.[END],TB1.rownum as row_1
    ,TB2.START,TB2.[END],TB2.rownum as row_2 FROM ....

To:

SELECT TB1.START,TB1.[END],(TB1.rownum + 1) as row_1
    ,TB2.START,TB2.[END],TB2.rownum as row_2 FROM ....

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

Your output seems fine to me. I don't know the best way to display this, but here's one way to visualize what is joining to what:

Table1.RowNum   +1      Table2.RowNum
1               2       2
2               3       3
3               4       4
4               5       5
5               6       null    /* no 6 in Table2.RowNum */
null            null    1       /* no 0 in Table1.RowNum */

In the last two rows here, see how there is no 6 in Table2.RowNum? Also, you can't add 1 to any Table1.RowNum and match Table2.RowNum = 1

Also, I'm not sure of the source of import_num in your query since you're not showing us that in your table definitions. This is a potential source of confusion in my mind.

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

Try this adding 1 to rownum while row_number, this will Join on 2,3,4,5.

SELECT TB1.START,TB1.[END],TB1.rownum as row_1,TB2.START,TB2.[END],TB2.rownum as row_2 FROM 
(
select START,[END],ROW_NUMBER() over(order by import_num) + 1 as rownum from 
table1 
) TB1
INNER JOIN
(
select START,[END],ROW_NUMBER() over(order by import_num) as rownum from 
table1 
) TB2
ON TB1.rownum  = TB2.rownum

Upvotes: 0

Related Questions