Jazimov Jazimov - 4 years ago 150
SQL Question

Querying Two Tables For Overlapping Ranges

I have two tables in Access 2007, each defines a ValueFrom and ValueTo column to express a range, such as from 1 to 10 or from 7 to 15.

I am trying to figure out how to issue a single query that would return the overlapping ranges in the two tables. I feel like I'm missing something...

Given these two tables:

Table01
ValueFrom ValueTo
1 10
5 15
25 50
500 600

Table02
ValueFrom ValueTo
1 12
16 24
30 40
45 100
500 600


I would want this as output

RESULTS FROM QUERY
Table01_ValueFrom Table01_ValueTo Table02_ValueFrom Table02_ValueTo
1 10 1 12
5 15 <emptystring> <emptystring>
25 50 30 40
25 50 45 100
500 600 500 600


The above results indicate range 1-10 in Table01 overlaps range 1-12 in Table 02; that range 5-15 in Table01 doesn't overlap any Table02 ranges a range in Table 02; that range 25-50 overlaps two rows in Table02, 30-40 and 45-100.

Notice that this query must detect partial and full range overlaps.

Any ideas on this one? It's okay to use psuedocode or even make a suggestion on how to approach this problem. While I can write a program to do the above, I am trying to solve with a single Access query.

Thank you!

Answer Source

In normal SQL, you could do this as a JOIN:

select t1.ValueFrom, t1.ValueTo, t2.ValueFrom, t2.ValueTo
from table01 as t1 left join
     table02 as t2
     on t1.ValueFrom <= t2.ValueTo and t1.ValueTo >= t2.ValueFrom;

Unfortunately, MS Access does not permit inequalities in the JOIN conditions. So, here is another way:

select t1.ValueFrom, t1.ValueTo, t2.ValueFrom, t2.ValueTo
from table01 as t1,
     table02 as t2
where t1.ValueFrom <= t2.ValueTo and t1.ValueTo >= t2.ValueFrom
union all
select t1.ValueFrom, t1.ValueTo, NULL, NULL
from table01 as t1
where not exists (select 1
                  from table02 as t2
                  where t1.ValueFrom <= t2.ValueTo and t1.ValueTo >= t2.ValueFrom
                 );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download