Join tableA.column1 to tableB where tableA.column1 is between tableB.column2 and tableB.column3

I have two tables: user, zone

# user table
CREATE TABLE `customers_users` (
    `userid` INT(10),
    `username` VARCHAR(12),
    `homePhone` VARCHAR(10)
)
# zone table
CREATE TABLE `zone` (
    `id` INT(10),
    `range_from` VARCHAR(10),
    `range_to` VARCHAR(10),
    `zone` VARCHAR(10)
)

The zone table defines what zones different telephone numbers fall under.

Given a zone table of:

| range_from | range_to   | zone   |
| 0260000000 | 0261000000 | Zone 1 |
| 0261000001 | 0262000000 | Zone 2 |
| 0262000001 | 0263000000 | Zone 3 |

The following would be the desired result :

| homePhone  | zone   |
| 0260000001 | Zone 1 |
| 0260000011 | Zone开发者_运维问答 1 |
| 0261000001 | Zone 2 |
| 0261000101 | Zone 2 |
| 0262000001 | Zone 3 |
| 0262001001 | Zone 3 |

The question is how do I join the user table to the zone table to acquire this result.

Join conditions are quite flexible:

select c.homePhone, z.zone
from customers_users as c
join zone as z on c.homePhone between z.range_from and z.range_to