CODEWITHSUNDEEP
mysqlsql
Lee
Lee

Reputation: 5936

Ranges on multiple columns

I have a table with 2 columns, cola and colb, which represent a range of numbers.

Example problem:

cola - colb
1    - 10
11   - 22
33   - 66
67   - 67

Example query: 

SELECT * 
  FROM example 
 WHERE vala >= cola 
   AND valb <= colb

The first criterion works great with the above query however it falls down when cola = colb ie.. 67 - 67.

The above is a simple example of my issue.. The problem is actually a range of Ips. Cola - Colb2 = Ip range. I have included in case something is missing in the above example. 

   SELECT *
     FROM `static_allocation`

    WHERE INET_ATON('$network_addr')   >= INET_ATON(network_addr)
      AND INET_ATON('$broadcast_addr') <= INET_ATON(broadcast_addr)

    LIMIT 1

Edit:

OK, it appears i am slightly wrong ..

Table data

10.0.0.0 - 10.0.0.15 
10.0.0.16 - 10.0.0.16

however when i try and select 10.0.0.16 - 10.0.0.20, it doesn't find the record..

Upvotes: 0

Views: 108

Answers (1)

Andomar
Andomar

Reputation: 238086

If you're looking for the first range that contains at least a part of the block, try a condition like:

vala <= colb and cola <= valb

This says the search range [vala,valb] must partially overlap with the target range [cola,colb].

In SQL:

select  *
from    example
where   vala <= colb and cola <= valb
order by
        cola -- Lowest network range
limit   1

Upvotes: 1

Related Questions