Compare Each Record to Every Other Record

[tbg130]

Hi All,

I have a database that has 3 columns in it (that matter for this discussion).

IP_LOW - the low number of an IP Range owned by a company
IP_HIGH - the high number of an IP Range owned by a company
IP_EXPRESSION - the regular expression of the entire range

I would like to company every IP_LOW to EVERY OTHER RANGE to make sure that it doesn't fit within another range. The UNIQUE function has allowed me to ensure no duplicates, but that doesn't mean that one IP_LOW couldn't fit between the IP_LOW and IP_HIGH of another entry for example.

Ideally, I would like to run a query that will identify/output the records that have an IP_LOW (or IP_HIGH for that matter) that fit within a range of other entries.

Any ideas / tips / tricks on how to accomplish this would be much appreciated.

Thanks in advance.

 

[vbkris]

check this out:

select IP_LOW,count(IP_LOW)
from
Table Original
Table Comparison
where
Original.IP_LOW between Comparison.IP_LOW and Comparison.IP_HIGH
OR
Original.IP_HIGH between Comparison.IP_LOW and Comparison.IP_HIGH
group by IP_LOW
having count(IP_LOW)>1


if that doesnt work, can you give me sample data so that i can check it out???

Known is handfull, Unknown is worldfull

 

[tbg130]

As I have the one table, I ran this code and it parsed but returned no results; which may be a good thing; however, I did enter 1 row that should show up as a range between another range...


select IP_LOW, count(IP_LOW)
from
OrgIPS
OrgIPS
where
OrgIPS.IP_LOW between OrgIPS.IP_LOW and OrgIPS.IP_HIGH
OR
OrgIPS.IP_HIGH between OrgIPS.IP_LOW and OrgIPS.IP_HIGH
group by IP_LOW
having count(IP_LOW)>1

Sample data would look like this:

999 12.175.112.110 12.175.112.200
953 198.165.163.0 198.165.163.255
957 198.103.152.0 198.103.153.255
957 198.103.162.0 198.103.162.255
957 198.103.167.0 198.103.167.255
957 198.103.172.0 198.103.172.255
957 198.103.182.0 198.103.182.255
957 198.103.221.0 198.103.221.255
957 198.103.223.0 198.103.223.255
957 198.103.249.0 198.103.249.255
958 66.207.118.0 66.207.127.255
960 134.216.0.0 134.216.255.255
963 24.222.0.0 24.222.255.255
1000 12.175.112.0 12.175.112.255


Record 999 should actually show up as a result because in falls within the range of record 1000.

I didn't get a return so more advice and help would be much appreciated.

Thanks alot!

 

[vbkris]

hmm, the solution for this is not that simple (as IPs are not numbers but varchar),

try this:
select IP_LOW, count(IP_LOW)
from
OrgIPS Tbl1
OrgIPS Tbl2
where
(
cast(replace(Tbl1.IP_LOW,'.','') as bigint) between
cast(replace(Tbl2.IP_LOW,'.','') as bigint) and
cast(replace(Tbl2.IP_HIGH,'.','') as bigint) and
)
OR
(
cast(replace(Tbl1.IP_HIGH,'.','') as bigint) between
cast(replace(Tbl2.IP_LOW,'.','') as bigint) and
cast(replace(Tbl2.IP_HIGH,'.','') as bigint) and
)
group by IP_LOW
having count(IP_LOW)>1


does that work???


Known is handfull, Unknown is worldfull

 

[tbg130]

This looks like it 'could' work but it is giving me an error for the 'OrgIPS' for some reason...

Other suggestions?

 

[vbkris]

what error???

Known is handfull, Unknown is worldfull

 

[tbg130]

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'OrgIPS'.

 

[vbkris]

oops
select IP_LOW, count(IP_LOW)
from
OrgIPS Tbl1
inner join
OrgIPS Tbl2
on
(
cast(replace(Tbl1.IP_LOW,'.','') as bigint) between
cast(replace(Tbl2.IP_LOW,'.','') as bigint) and
cast(replace(Tbl2.IP_HIGH,'.','') as bigint) and
)
OR
(
cast(replace(Tbl1.IP_HIGH,'.','') as bigint) between
cast(replace(Tbl2.IP_LOW,'.','') as bigint) and
cast(replace(Tbl2.IP_HIGH,'.','') as bigint) and
)
group by IP_LOW
having count(IP_LOW)>1


Known is handfull, Unknown is worldfull