Re: Question Join/Subselect

On Thursday 28 August 2003 02:20 am, Alex wrote:
> Andrew L. Gould wrote:
> >On Wednesday 27 August 2003 10:28 pm, Alex wrote:
> >>Hi,
> >>
> >>I have some problems with creating a query that will replace values in
> >>one table from another one.
> >>
> >>Table 1:
> >>userName : refCode1 : refCode2
> >>------------------------------
> >>alex     :  12      : 24
> >>
> >>Table 2:
> >>refCode :  ActualCode
> >>---------------------
> >>12          AAAAAA
> >>24          BBBBBB
> >>
> >>
> >>Result Desired
> >>userName : refCode1 : refCode2
> >>------------------------------
> >>alex     :  AAAAAA  : BBBBBB
> >>
> >>
> >>I need to crete a view that returns me Table1 but replaces refCode1,
> >>refCode2 with the ActualCode.
> >>
> >>I did not have any success with Joins or Subselects so far.
> >>
> >>Thanks for any advise
> >>
> >>Alex
> >
> >I'm not sure how to handle the space in the table names.  If there weren't
> > any spaces in table names, the following should work:
> >
> >select Table1.userName, Table2.ActualCode, Table3.ActualCode
> >from Table1, Table2, Table2 as Table3
> >where Table1.refCode1 = Table2.refCode and Table1.refCode2 =
> > Table3.refCode;
> >
> >I hope this helps,
> >
> >Andrew Gould
>
> Andrew,
> thanks for the help. The query actually works. However if I try to
> create a view then the sever complains
>
> Create Tabe: attribute "actualcode" duplicated;
>
> Any Ideas on how to get around that ?
>
> Alex

Alex,

Have you tried renaming the column being duplicated?

select Table1.userName, Table2.ActualCode as ActualCode1, Table3.ActualCode as
ActualCode2
from Table1, Table2, Table2 as Table3
where Table1.refCode1 = Table2.refCode and Table1.refCode2 =
Table3.refCode;

Best of luck,

Andrew Gould