Outer Join on a between value? Can it be done?

I have a database that we prototyped in Access originally. The tables were built with the idea of specifing that specific IP ranges covered a set area.

Example:

Area Ocb1 Ocb2 Ocb3 Ocb4 Oce1 Oce2 Oce3 Oce4

A1       10    100   1     1     10    100   224   255
A2       10    100   225   1     10    100   225   255
A3       10    120   1     1     10    120    1    255

Where Ocb1-4 and Oce1-4 are the beginning and ending ranges of each IP octet respectively.

Each device with an IP was stored in its table as such.

Device Oc1 Oc2 Oc2 Oc4

D1      10   100  105   2
D2      10   100  225   70
D3      10   110  210   12

Now, i need a query that would return something like:

Device Area

D1       A1
D2       A2
D3       NULL

In access we wrote the query as such

Select Device, Area from DeviceTbl D Left join IPTable I ON ((D.Oc1 between I.Ocb1 and Oce1) AND (D.Oc2 between I.Ocb2 and Oce2) AND (D.Oc3 between I.Ocb3 and Oce3) AND (D.Oc4 between I.Ocb4 and Oce4))

This worked in access, but i cannot seem to figure out how to do it in Oracle.

Currently all i can do is

Select Device, Area from DeviceTbl D , IPTable I Where ((D.Oc1 between I.Ocb1 and Oce1) AND (D.Oc2 between I.Ocb2 and Oce2) AND (D.Oc3 between I.Ocb3 and Oce3) AND (D.Oc4 between I.Ocb4 and Oce4))

But this doesn't give me the null results.

Any ideas?

Thanks,

Rob Edwards Received on Tue Jul 09 2002 - 14:52:05 CDT