How to make multiple BETWEEN statements not knowing the quantity of them beforehand? (detecting country by IP range)

Question

I have Ip2LocationLite database with ip_from, ip_to, country_code columns.

All IP's are in decimal format (converted using a function).

So I have to detect those users with IP addresses from certain country. For example, Russia has over 3 thousands IP ranges and I want to search through all of them.

Script looks like this:

SELECT * FROM [dbo].[USERINFO] as UserInfo
WHERE (SELECT gaminator.dbo.IPAddressToInteger (UserInfo.LastIp)) BETWEEN
    (
        SELECT [ip2location].[dbo].[ip2location_db1].ip_from FROM [ip2location].[dbo].[ip2location_db1]
        WHERE [ip2location].[dbo].[ip2location_db1].country_code = 'RU'
    )
    AND
    (
        SELECT [ip2location].[dbo].[ip2location_db1].ip_to FROM [ip2location].[dbo].[ip2location_db1]
        WHERE [ip2location].[dbo].[ip2location_db1].country_code = 'RU'
    )

The last thing gives error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This is because BETWEEN expects one diapason instead of 3 thousands diapasons.

How to program this so it makes 3 thousands BETWEEN's instead? (according to the number of diapasons to be checked)

Answer 1

It can be a little tricky to answer these questions without a few sample records and the expected output. But if I've understood your schema correctly you can use a join instead of the sub queries.

Example

SELECT 
    * 
FROM 
    [dbo].[USERINFO] as UserInfo
        INNER JOIN [ip2location].[dbo].[ip2location_db1] AS ipRange     ON UserInfo.LastIp BETWEEN ipRange.ip_to AND ipRange.ip_from
WHERE   
    ipRange.country_code = 'RU'
;