Re: How to address field names in a join query

Denis Gasparin <denis@edistar.com> writes:
> The question is: how can i refer to the fields DESCRIPTION of each table?

Either leave off the join's alias, or extend it to rename the columns as
well as the joined table.  Aliasing hides the table names that were
inside the join, so you're stuck if you haven't renamed the columns.
Example:

create table int8_tbl (q1 int8, q2 int8);

select a.q1,b.q2 from
(int8_tbl a inner join int8_tbl b on a.q1=b.q1);

select a.q1,b.q2 from
(int8_tbl a inner join int8_tbl b on a.q1=b.q1) x;    -- WRONG

select aq1, x.bq2 from
(int8_tbl a inner join int8_tbl b on a.q1=b.q1) x(aq1,aq2,bq1,bq2);

I suspect that the first variant may be illegal per the letter of the
SQL spec (since the joined table has duplicate column names), but
Postgres doesn't enforce any such restriction.

            regards, tom lane