Numeric range between numeric range

Question

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.

So you want to perform a join between the two tables based on the ranges overlapping? Just to be clear, what are the rules for the endpoints of the ranges? I.e. if table1 has a range 0..15000, does that overlap with a range in table2 of 15000...25000? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented May 23, 2017 at 14:02
@Damien_The_Unbeliever sorry for not let that clear, no, I'm not counting with overlap — Dibort
– Dibort, Commented May 23, 2017 at 14:13

Juan Carlos Oropeza · Accepted Answer · 2017-05-23 14:02:07Z

4

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

answered May 23, 2017 at 14:02

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Numeric range between numeric range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related