"Kathy" <r**@roo.com> schrieb im Newsbeitrag
news:YL-dneeNKdIulVjfRVn-vg@is.co.za...
> Hi All
>
> For those of you that have not perhaps seen this - I need to join and
> lookup on a range of values
> Does anyone have a better / quicker cleverer way of doing this
>
> ok here is the scenario
>
> We have a lookup table with IP addresses and Countries
> These IP addresses are converted to an IP number and they exist in a range
> i.e. IP_TO 680000        IP_From 680100
>
> We have a table with a list of our users IPs
> We convert the IP addresses in the users tables to a number
>
> Then we have to join the user table to the Lookup table to map the IP's to
> the countries because we need a list of all IP's with their countries
>
> The join I have is the only join I need to make but because it is a range
> one has to use between which may not be the best way to do this
> Keep in mind however that the list of user IP's is 11 million plus so the
> table is huge and we really need an effcient way to do this whether it is
> via an index or something else
>
> Here are the DDL's
>
>
> CREATE TABLE [IP_Addr_Work] (
> [IPAddressId] [int] NOT NULL ,
> [IPNumber] [bigint] NULL ,
> [IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [IPStatus] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [IP_Address_City_Lkp] (
> [IP_Address_From] [bigint] NOT NULL ,
> [IP_Address_To] [bigint] NOT NULL ,
> [Country_Short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country_Long] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
>
> This is the join I need to do
>
> SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus
>  , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region,
> iacl.City
> FROM StagingDatabase.dbo.IP_Addr_Work AS iaw
> LEFT JOIN dbo.IP_Address_City_Lkp AS iacl
>  ON IPNumber BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To
>
>
>

"Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eXMpPAifFHA.2700@TK2MSFTNGP15.phx.gbl...
> Just take that:
>
>> SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus
>>  , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region,
>> iacl.City
>> FROM StagingDatabase.dbo.IP_Addr_Work AS iaw
>> LEFT JOIN dbo.IP_Address_City_Lkp AS iacl
>>  ON IPNumber >= iacl.IP_Address_From AND IPNumber <= iacl.IP_Address_To
>
> Sorry but don´t have any productional system to test this right now.
>
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
> "Kathy" <r**@roo.com> schrieb im Newsbeitrag
> news:YL-dneeNKdIulVjfRVn-vg@is.co.za...
>> Hi All
>>
>> For those of you that have not perhaps seen this - I need to join and
>> lookup on a range of values
>> Does anyone have a better / quicker cleverer way of doing this
>>
>> ok here is the scenario
>>
>> We have a lookup table with IP addresses and Countries
>> These IP addresses are converted to an IP number and they exist in a
>> range
>> i.e. IP_TO 680000        IP_From 680100
>>
>> We have a table with a list of our users IPs
>> We convert the IP addresses in the users tables to a number
>>
>> Then we have to join the user table to the Lookup table to map the IP's
>> to
>> the countries because we need a list of all IP's with their countries
>>
>> The join I have is the only join I need to make but because it is a range
>> one has to use between which may not be the best way to do this
>> Keep in mind however that the list of user IP's is 11 million plus so the
>> table is huge and we really need an effcient way to do this whether it is
>> via an index or something else
>>
>> Here are the DDL's
>>
>>
>> CREATE TABLE [IP_Addr_Work] (
>> [IPAddressId] [int] NOT NULL ,
>> [IPNumber] [bigint] NULL ,
>> [IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [IPStatus] [int] NULL
>> ) ON [PRIMARY]
>>
>> CREATE TABLE [IP_Address_City_Lkp] (
>> [IP_Address_From] [bigint] NOT NULL ,
>> [IP_Address_To] [bigint] NOT NULL ,
>> [Country_Short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Country_Long] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Region] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [City] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>>
>> This is the join I need to do
>>
>> SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus
>>  , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region,
>> iacl.City
>> FROM StagingDatabase.dbo.IP_Addr_Work AS iaw
>> LEFT JOIN dbo.IP_Address_City_Lkp AS iacl
>>  ON IPNumber BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To
>>
>>
>>
>
>