Thread: alias problem on join

Hi folks,

I've got an accounts table and a transactions table and want to do a summary 
join.  If I do:

bank=# select aid, aname, aodraft from accounts a
bank-# left outer join (select account, sum(amount) as balance
bank(# from statement group by account) as s
bank-# on s.account = a.aid;

I get the results I want, but I need to rename the aid column to 'key' so that 
I can feed it into a standard routine I have in PHP to build a hash.  If I 
do:

bank=# select aid as key, aname, aodraft from accounts a
bank-# left outer join (select account, sum(amount) as balance
bank(# from statement group by account) as s
bank-# on s.account = a.key;

I get 

ERROR:  No such attribute or function 'key'

Any ideas why?

Also, I'm sure I can do this more efficiently as a single select/join, but 
can't seem to work out why (Friday morning syndrome).  Anyone give me a clue?
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

Dnia 2004-02-06 12:38, Użytkownik Gary Stainburn napisał:
> Hi folks,
> 
> I've got an accounts table and a transactions table and want to do a summary 
> join.  If I do:
> 
> bank=# select aid, aname, aodraft from accounts a
> bank-# left outer join (select account, sum(amount) as balance
> bank(# from statement group by account) as s
> bank-# on s.account = a.aid;
> 
> I get the results I want, but I need to rename the aid column to 'key' so that 
> I can feed it into a standard routine I have in PHP to build a hash.  If I 
> do:
> 
> bank=# select aid as key, aname, aodraft from accounts a
> bank-# left outer join (select account, sum(amount) as balance
> bank(# from statement group by account) as s
> bank-# on s.account = a.key;

This is an output column alias and it can't be used in where clause.
In where clause you have to use original column name and the only 
aliases you can use there are table aliases.

Regards,
Tomasz Myrta