Joining table on a range

DBDave

Hello,

I have a rather rare issue. I have 2 tables. Let's call them IP and ICP. These tables hold IP addresses. I need to join these 2 tables, but my only join condition is a range of IP's.

Let me explain...

In table IP we have a column called IPAddress. It's self explanatory what this column contains. In the ICP table there are 2 columns... IPAddress_start and IPAddress_end. So, I need to join the IPAddress from the IP table to the range between IPAddress_start and IPAddress end.

I have tried the following that hasn't worked. I also tried SUBQUERIES, but the query returns more than 1 row so I get an error message.

Any help would be greatly appreciated.

SELECT * FROM IP
JOIN ICP
ON IP.IPAddress >= ICP.IPAddress_start
OR IP.IPAddress <= ICP.IPAddress_end;

SELECT * FROM IP
WHERE EXISTS (SELECT * FROM ICP WHERE
IP.IPAddress >= ICP.IPAddress_start OR
IP.IPAddress <= ICP.IPAddress_end);

skeswani

https://my.vertica.com/blog/getting-rid-range-joins/

DBDave

Thank you. The BETWEEN worked. I didn't think it would