Re: Table Join Help Needed...

<heizer1_at_llnl.gov> wrote in message
news:1140065353.134693.19170_at_g43g2000cwa.googlegroups.com...
> Hello,
> I need help joining two tables with similar data but not exact.
>
> Table 1: ADMSUB
> Two Columns: SUBNET, DIRECTORATE_CODE
>
> Example Entries...
>
> 128_112_33_ , EED
> 128_112_12_ , EED
> 128_116_14_ , DTD
>
> Table 2: DEVICES
> Three Columns: DEVICE_ID, OS, OS_VER
>
> Example Entries...
>
> 128_112_33_12 , LINUX , 2.4
> 128_112_12_12 , WINDOWS , XP
> 128_116_14_1 , MAC , OSX
>
> I would like to join these two tables so that the output looks like
> this...
>
> 128_112_33_12 , LINUX , 2.4 , 128_112_33_ , EED
> 128_112_12_12 , WINDOWS , XP , 128_112_12_ , EED
> 128_116_14_1 , MAC , OSX , 128_116_14_ , DTD
>
> Everytime I try a query with a outer join or something I end up with
> nulls, can someone please tell me what I'm doing wrong.
>
> Thanks,
> - Charles
>

ugh. Fixing the data model is a good idea. It looks like you are storing multiple attributes in one column and that is messing you up. this might work
select ... from devices, admsub where
devices.substr(device_id,1,11)=admsub.subnet

but before you do that I would do
create index devices_key on devices(substr(device_id,1,11)) ... compute statistics;

(to make the join efficient)
Jim Received on Wed Feb 15 2006 - 22:58:34 CST