"Column Ambiguously Defined" in Oracle

Question

Currently trying to create a simple join in Oracle but it claims that the "Column Ambiguously Defined".

After research, we have prefaced the column with their table names but it continues to throw this error.

Here's our statement:

SELECT DOCTOR_ID, TYPEOFCARE FROM DOCTOR
INNER JOIN PRIMARY_CARE
ON DOCTOR.DOCTOR_ID = PRIMARY_CARE.DOCTOR_ID;

Also provided is an image of our er diagram showing the two tables and their relationship:

Any help is much appreciated.

The query optimizer isn't smart enough to realize that the two `doctor_id`s will be the same in the select part of the statement; you need to qualify it with one of the tables there as well. — Powerlord, Feb 29 '20 at 20:23

score 1 · Accepted Answer · answered Feb 29 '20 at 21:06

If a column name appears in more than one table you need to specify the table name. In this case it doesn't make any difference which you choose.

SELECT DOCTOR.DOCTOR_ID, TYPEOFCARE FROM DOCTOR
INNER JOIN PRIMARY_CARE
ON DOCTOR.DOCTOR_ID = PRIMARY_CARE.DOCTOR_ID;

1 Answers1