Thread: Select for LEFT JOIN

Hi,

I am trying to do a 2 joins between 3 tables.

ie) 
select <some field>, coalesce(TRR.ABC, SOC.ABC) as newABC, ...
from A join (B join C on (..)) on (..)) as TRR        left join 
(D join E on (..)) as SOC on (TRR.Field1=SOC.Field2)

When I run this it says that there is an ambiguous field.  Yes after the
join for TRR and SOC they both contain a fields name ABC.  How can I
rename this field in the sql statement or how can I make it so the sql
statement know that they are different.

Please help.. .thanks

-- 
Linh Luong
Computalog Ltd.
Software Developer
Phone: (780) 464-6686 (ext 325)
Email: linh.luong@computalog.com

>>>>> "LL" == Linh Luong <linh.luong@computalog.com> writes:
LL> Hi,LL> I am trying to do a 2 joins between 3 tables.
LL> ie) LL> select <some field>, coalesce(TRR.ABC, SOC.ABC) as newABC, ...LL> from A join (B join C on (..)) on (..))
asTRR LL> left join LL> (D join E on (..)) as SOC on (TRR.Field1=SOC.Field2)
 
LL> When I run this it says that there is an ambiguous field.  Yes after theLL> join for TRR and SOC they both contain
afields name ABC.  How can ILL> rename this field in the sql statement or how can I make it so the sqlLL> statement
knowthat they are different.
 
LL> Please help.. .thanks

You have to use full-qualifyed field names, like this:

select trr.abc, soc.abc,....

In addition you can use aliases for field names:

select trr.abc as abc1, soc.abc as abc2, ....

-- 
Anatoly K. Lasareff                 Email:    tolik@aaanet.ru
http://tolikus.hq.aaanet.ru:8080