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