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

raedwa01_at_hotmail.com (Rob Edwards) wrote in message news:<8ea2ecb0.0207091152.cea3f2d_at_posting.google.com>...
> 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

The outer join operator in Oracle is (+).

SELECT D.device, I.area
FROM DeviceTbl D , IPTable I

WHERE D.oc1 BETWEEN I.ocb1 (+) AND I.oce1 (+)
AND   D.oc2 BETWEEN I.ocb2 (+) AND I.oce2 (+)
AND   D.oc3 BETWEEN I.ocb3 (+) AND I.oce3 (+)
AND   D.oc4 BETWEEN I.ocb4 (+) AND I.oce4 (+)

will return
Device Area

D1       A1
D2       A2

SELECT D.device, I.area
FROM DeviceTbl D , IPTable I

WHERE D.oc1 (+) BETWEEN I.ocb1 AND I.oce1
AND   D.oc2 (+) BETWEEN I.ocb2 AND I.oce2
AND   D.oc3 (+) BETWEEN I.ocb3 AND I.oce3
AND   D.oc4 (+) BETWEEN I.ocb4 AND I.oce4 

will return
Device Area

D1       A1
D2       A2
         A3 

HTH
//Rauf Sarwar Received on Tue Jul 09 2002 - 23:14:59 CDT