Обсуждение: How to address field names in a join query

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?

Sorry for the english, I hope you understand anyway...




Doct. Eng. Denis
Gasparin                                             denis@edistar.com
---------------------------------------------------------------------------------------
Programmer & System
Administrator                                http://www.edistar.com
---------------------------------------------------------------------------------------
                              Well alas we've seen it all before
                               Knights in armour, days of yore
                          The same old fears and the same old crimes
                            We haven't changed since ancient times

                               -- Iron Hand -- Dire Straits --
---------------------------------------------------------------------------------------

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.

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