Cross Table Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cross Table Query

Author

Topic

Starlet_GT
Yak Posting Veteran

81 Posts

Posted - 2011-07-19 : 08:10:03

Hi everyone,

I am trying to fetch data from two tables where i have to compare IP address in both tables.

In my table_A there is one IP address column which should be compared with table_B IP range. for example in table A IP column contains data as follows.

135.211.85.7
216.155.170.71
142.146.244.59
135.211.85.44
145.7.45.39

in table_B there are two columns as follows

IP_Start -------IP_End
194.98.43.65----194.98.43.126
194.98.42.129---194.98.42.142
193.201.151.65--193.201.151.126
208.138.44.1----208.138.44.30
94.201.224.1----94.201.224.254
94.201.225.1----94.201.225.254

I need to get data where table_A ip falls in table_b IP range so i wrote a query as follows

select (somedata)
from table_a, table_b
WHERE LTRIM(RTRIM(REPLACE(A.IPAddress,'.',''))) >= RTRIM(LTRIM(REPLACE(B.IP_Start,'.','')))
AND RTRIM(LTRIM(REPLACE(A.IPAddress,'.',''))) <= RTRIM(LTRIM(REPLACE(B.IP_End,'.','')))

out of 60,000 records only half of result come with sucess. what i am doing wrong. please help

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-19 : 08:45:13

Firstly, you cannot compare IP addresses in this manner. Read up on how IP addresses are constructed and you will understand why your technique doesn't work. The easiest way to perform operations on IP addresses is to convert them into their base integer format, following the formula: (first octet * 16777216) + (second octet * 65536) + (third octet * 256) + (fourth octet). This will allow you to run direct comparisions and other operations such as range lookups (which is what you are trying to do here). Keep in mind that if you need to support IPv6, be prepared to use BIGINT instead of INT.

Secondly, the recommended format for using a JOIN between two tables is to use the syntax "INNER JOIN...ON", instead of just a comma between the two tables. It will result in cleaner, easy-to-maintain code.

OS

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-19 : 08:48:41

You would need to pad the individual sections so they are all the same length:


Declare @ta table (
	ip varchar(15)
)
Insert Into @ta Select '135.211.85.7' 
Insert Into @ta Select '216.155.170.71' 
Insert Into @ta Select '142.146.244.59' 
Insert Into @ta Select '135.211.85.44'
Insert Into @ta Select '145.7.45.39'

Declare @tb table (
	rangeId int identity(1,1),
	ipStart varchar(15),
	ipEnd varchar(15)
)

Insert Into @tb Select '194.98.43.65', '194.98.43.126'
Insert Into @tb Select '194.98.42.129', '194.98.42.142'
Insert Into @tb Select '193.201.151.65', '193.201.151.126'
Insert Into @tb Select '208.138.44.1', '208.138.44.30'
Insert Into @tb Select '94.201.224.1', '94.201.224.254'
Insert Into @tb Select '94.201.225.1', '94.201.225.254'
--Insert Into @tb Select '0.0.0.0', '255.255.255.255'
Insert Into @tb Select '135.211.85.0', '135.211.87.255'
Insert Into @tb Select '135.211.85.5', '135.211.87.255'


select *
from @ta A, @tb B
WHERE LTRIM(RTRIM(REPLACE(A.ip,'.',''))) >= RTRIM(LTRIM(REPLACE(B.ipStart,'.','')))
AND RTRIM(LTRIM(REPLACE(A.ip,'.',''))) <= RTRIM(LTRIM(REPLACE(B.ipEnd,'.','')))



select *
from 
	(
	Select 	
		ip = right('000'+parsename(ip,4),3)+'.'+
			right('000'+parsename(ip,3),3)+'.'+
			right('000'+parsename(ip,2),3)+'.'+
			right('000'+parsename(ip,1),3)
	From @ta
	) A
Left Join 
	(	
	Select
		rangeId,
		ipStart = right('000'+parsename(ipStart,4),3)+'.'+
			right('000'+parsename(ipStart,3),3)+'.'+
			right('000'+parsename(ipStart,2),3)+'.'+
			right('000'+parsename(ipStart,1),3),
		ipEnd = right('000'+parsename(ipEnd,4),3)+'.'+
			right('000'+parsename(ipEnd,3),3)+'.'+
			right('000'+parsename(ipEnd,2),3)+'.'+
			right('000'+parsename(ipEnd,1),3)
	From @tb
	) B
On A.ip between B.ipStart and B.ipEnd

Corey

I Has Returned!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-19 : 08:56:46

Or,

ipStart = 16777216 * parsename(ipStart, 4) + 65536 * parsename(ipStart, 3) + 256 * parsename(ipStart, 2) + 1 * parsename(ipStart, 1),
ipEnd = 16777216 * parsename(ipEnd, 4) + 65536 * parsename(ipEnd, 3) + 256 * parsename(ipEnd, 2) + 1 * parsename(ipEnd, 1)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources