Re: How to address field names in a join query

On Mon, 26 Nov 2001, Denis Gasparin wrote:

> I have 3 tables with some fields with the same name. For example:
>
> TABLE_A: PR_CODE, DESCRIPTION, IS_VALID
>
> TABLE_B: PR_ST_CODE, PR_CODE, DESCRIPTION
>
> TABLE_C: PR_VD_CODE, PR_CODE, DESCRIPTION
>
> As you see, the DESCRIPTION field is present in all tables.
> Now suppose i want to do query like this:
>
> select * from (table_a a inner join table_b b on a.pr_code = b.pr_code) x
> inner join table_c c on x.pr_code = c.pr_code
>
> I obtain a resultset with these fields:
> PR_CODE | DESCRIPTION | IS_VALID | PR_ST_CODE | DESCRIPTION | PR_VD_CODE |
> DESCRIPTION
>
> The question is: how can i refer to the fields DESCRIPTION of each table?
>
> I can refer to table_c DESCRIPTION with c.DESCRIPTION but if i write
> x.DESCRIPTION postgresql cannot identy the true field names because
> actually there are two x.DESCRIPTION fields... How can i refer to these?

At least on current sources (don't have 7.1 here to test) you can give x
a column list (in position order) which allows you to rename columns from
the first join so you can rename the descriptions to something like
A_Description and B_Description.