Re: ambiguous

On Wed, Mar 07, 2001 at 07:38:32AM -0800, si wrote:
> If I assign the same name to 2 columns in 2 different tables:
>
> +--------------------------------------+
> |              DEPARTMENT              |
> +----------+----------------+----------+
> | LOCATION |  DESCRIPTION   |  DEPT_NO |
> +----------+----------------+----------+
> | Bedrock  | Administration |     1    |
> +----------+----------------+----------+
>
> +--------------------------------+
> |            EMPLOYEE            |
> +---------+------------+---------+
> | EMPL_ID | NAME_LAST  | DEPT_NO |
> +---------+------------+---------+
> |    1    | Slate      |     1   |
> +---------+------------+---------+
>
> select * from DEPARTMENT, Employee where dept_no = '1';
>
> PG throws up:
> ERROR:  Column 'dept_no' is ambiguous
>
> Is this not allowed? or is my sql understanding wrong?

Do you want to join the two tables? If you want output roughly like:

LOCATION | DESCRIPTION    | DEPT_NO | EMPL_ID | NAME_LAST
---------+----------------+---------+---------+----------
Bedrock  | Administration | 1       | 1       | Slate
Bedrock  | Administration | 1       | 2       | Rubble

Then you want a query like:

select * from department natural join employee
where employee.dept_no = '1';

You have to give the table name in the where clause, even when you're
using the same field that you're joining on.

Richard