CODEWITHSUNDEEP
sql-servert-sqlrange
Dibort
Dibort

Reputation: 113

Numeric range between numeric range

I have a problem that I don't know if I can solve in just one select.

I have a list of ranges that I need to match with another list of ranges, any of the numbers of the first range...

Let me try to explain:

Table 1:                 Table 2

|   From |     To |     |   From |      To |
|--------|--------|     |--------|---------|
|      0 |   2000 |     |      0 |   15000 |
|   2001 |  10000 |     |  15000 |   25000 |
|  10001 |  50000 |     |  25000 |   50000 |
|  50001 | 200000 |     |  50000 |  200000 |
| 200001 | 500000 |     | 200000 |  400000 |
| 500001 |      0 |     | 400000 |  700000 |
                        | 700000 | 1500000 |

[Table 1] first row (0 - 2000) is easy, that matches with [Table 2] first row (0 - 15000).

But I need that the third row (and any other similar cases like row 5) of [Table 1] (10001 - 50000) match the first three rows of [Table 2] because:

The numbers in this range 10001 - 50000 are between 0 to 15000 but also in 15000 to 25000 and 25000 to 50000...

The result desired should be like that:

Table result:

| From (Table 1) | To (Table 1) | From (Table 2) | To (Table 2) |
|----------------|--------------|----------------|--------------|
|              0 |         2000 |              0 |        15000 |
|           2001 |        10000 |              0 |        15000 |
|          10001 |        50000 |              0 |        15000 |
|          10001 |        50000 |          15000 |        25000 |
|          10001 |        50000 |          25000 |        50000 |
| etc...

My intention is to it all in SQL without SQL functions.

Upvotes: 1

Views: 116

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

To see if match overlap you do like this. 

SELECT T1.*, T2.*
FROM Table1 T1
JOIN Table2 T2
  ON T1.From < T2.To
 AND T1.To   > T2.From

EDIT: use >= if the border ranges count to overlapping

Upvotes: 4

Related Questions