Postgres Pro
Downloads
Home   >   mailing lists

Thread: ambiguous

ambiguous

From
si
Date:
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?


_____________________________________________________________
Pick up your email anywhere in the world ---> http://www.remail.net

RE: ambiguous

From
"Riebs, Andy"
Date:
> 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?

While a human can determine that it doesn't matter if you use
DEPARTMENT.dept_no or Employee.dept_no in this query, it would
require an extremely talented parser to recognize that.

What you probably want is something like

  select * from DEPARTMENT, Employee where DEPARTMENT.dept_no=1
  and DEPARTMENT.dept_no=Employee.dept_no;

/andy

---
Andy Riebs, andy.riebs@compaq.com      Alpha Technology Solutions Group
978-506-6628, fax 978-506-6524         Compaq Computer Corporation
(h) ariebs@earthlink.net               <http://www.linuxalpha.compaq.com>
<http://osdb.sourceforge.net/>

Re: ambiguous

From
"Brett W. McCoy"
Date:
On Wed, 7 Mar 2001, 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?

It's correct behavior, because it can't differentiate between the two
fields with the same name.  But this shouldn't present a problem if you
are using dept_no as a join field.  You will want to select things this
way:

select d.*, e.empl_no, d.name_last from DEPARTMENT d, Employee e where
e.dept_no = d.dept_no and d.dept_no = 1;

It's longer SQL, but it's also more precise.

-- Brett
                http://www.chapelperilous.net/~bmccoy/
------------------------------------------------------------------------
A bird in the hand is worth what it will bring.

Re: ambiguous

From
Richard Poole
Date:
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