outer join

[Ruslan Zasukhin]

on 10/20/03 18:14, Yukun Gou at ygou at fiberlink.com wrote:

Hi Yukun,

> Hello,
> I am a newbie and have an outer join question:
> 
> I have three tables t1, t2 and t3, and there are three outer joins between
> t1 and t2:
> o1: t1.PROVIDER_ID *= t2.PROVIDER_ID
> o2: t1.ISP_ID *= t2.ISP_ID
> o3: t1.COUNTRY_CD *= t2.COUNTRY_CD
> 
> 
> My SELECT statement DOESN'T work with o1, o2 and o3 together. If I remove
> any one or two of o1, o2 and o3, the SELECT statement works fine.
> 
> Is there any limitation, restriction, etc., on outer joins and what is the
> solution? Or is anything wrong with my select statement?
> 
> 
> Here is the SELECT statement.
> 
> SELECT            t1.POP_DETAIL_ID,
>           t1.AREA_CD,
>           t1.EXCHANGE_CD,
>           t1.PHONE_NUMBER,
>           t1.MAX_BAUD,
>           t1.POP_COST,
>           t1.PROVIDER_ID,
>           t1.PRIVATE_RAS,
>           t1.IS_TOLL_FREE,
>           t1.IS_FLAT_RATE_ANALOG,
>           t2.ORDER,
>           t3.CITY_NAME
> 
> FROM        t1,t2,t3
> 
> WHERE             t1.IS_EXCLUDED=FALSE AND
>           (     t1.CONNECTION_TYPE = 40 OR
>                 t1.CONNECTION_TYPE = 32 OR
>                 t1.CONNECTION_TYPE = 33
>           ) 
                AND
>           t1.PROVIDER_ID *= t2.PROVIDER_ID  AND
>           t1.ISP_ID *= t2.ISP_ID  AND
>           t1.COUNTRY_CD *= t2.COUNTRY_CD AND
>           t1.CITY_CD = t3.CITY_CD
> 
> ORDER BY    t2.ORDER

Actually Valentina cannot do JOIN (inner or outer) based
on 2-fields link.

As I see this is exactly what you try to do.
Even more, you try do 3-field link:

           t1.PROVIDER_ID *= t2.PROVIDER_ID  AND
           t1.ISP_ID *= t2.ISP_ID  AND
           t1.COUNTRY_CD *= t2.COUNTRY_CD AND
    
Yukun, why so complex?

Why not to establish link by just one field ?

May be you still can use your 3 fields to make logical link,
But also you can add ObjectPtr to establish physical link between records.

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------