CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pradeep Kumar
Pradeep Kumar

Reputation: 6979

Select Records between Range from another table

I have two tables, say Table1 and Table2

Table1

╔════╦════╗
║ ID ║ RN ║
╠════╬════╣
║ 11 ║ 1  ║
║ 12 ║ 2  ║
║ 13 ║ 3  ║
║ 14 ║ 4  ║
║ 15 ║ 5  ║
║ 16 ║ 6  ║
║ 17 ║ 7  ║
║ 18 ║ 8  ║
║ 19 ║ 9  ║
║ 10 ║ 10 ║
╚════╩════╝

Table2

╔════╦════════╦══════╗
║ ID ║ FromRN ║ ToRN ║
╠════╬════════╬══════╣
║  1 ║    1   ║  3   ║
║  2 ║    6   ║  8   ║  
║  3 ║    10  ║  10  ║
╚════╩════════╩══════╝

I want all those records from Table1 whose RN lies between any range between FromRN and ToRN in Table2

So my expected output is:

╔════╦════╗
║ ID ║ RN ║
╠════╬════╣
║ 11 ║ 1  ║
║ 12 ║ 2  ║
║ 13 ║ 3  ║
║ 16 ║ 6  ║
║ 17 ║ 7  ║
║ 18 ║ 8  ║
║ 10 ║ 10 ║
╚════╩════╝

SQLFiddle to create schema can be found here:

http://sqlfiddle.com/#!3/90d50

Upvotes: 7

Views: 7206

Answers (4)

Snehlata Thakur
Snehlata Thakur

Reputation: 1

You can try in this way too.

select t1.ID,t1.RN 
from Table1 as t1,Table2 as t2
where t1.RN between t2.FromRN and ToRN ;

Upvotes: 0

Carra
Carra

Reputation: 17964

You can try this:

select t1.Id, t1.RN
from table1 t1
inner join table2 t2
on t1.Rn between t2.FromRn and t2.ToRn

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You can do an INNER JOIN of the two tables to filter out those records from Table1 whose RN values do not fall into any range in Table2:

SELECT t1.ID, t1.RN
FROM Table1 t1
INNER JOIN Table2 t2
    ON t1.RN >= t2.FromRN AND t1.RN <= t2.ToRN

Follow the link below for a running demo (courtesy of the OP):

SQLFiddle

Upvotes: 14

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

And another solution is using EXISTS - 

SELECT
*
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2
    WHERE t1.rn >= FromRN
    AND t1.rn <= ToRN)

Result

| ID | RN |
|----|----|
| 11 |  1 |
| 12 |  2 |
| 13 |  3 |
| 16 |  6 |
| 17 |  7 |
| 18 |  8 |
| 20 | 10 |

Upvotes: 4

Related Questions