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