ORA-00918: column ambiguously defined

Question

I am trying to retrieve some data (coursename) from one of my tables but the following error is coming all the time

ORA-00918: column ambiguously defined

the command I am typing is:

select bookno,courno,coursename
from booking, course,coursename
where bookno = 6200
and booking.courno = course.courno
and coursename.coursenameno = course.coursenameno

I have some tables as described :

CREATE TABLE BOOKING 
    (BOOKNO NUMBER (4) NOT NULL,
     COURNO NUMBER (4) NOT NULL,
     BOOKDATE DATE,
     BOOKCUSTPAYMENT VARCHAR (20),
     CONSTRAINT PK_BOOK PRIMARY KEY (BOOKNO,COURNO),
     CONSTRAINT FK_BOOK FOREIGN KEY (COURNO) REFERENCES COURSE(COURNO)
     ON DELETE CASCADE);

CREATE TABLE CUSTOMER
    (CUSTNO NUMBER (4) NOT NULL, -- creation of primary-key 
     PROFNO NUMBER (4) NOT NULL, 
     CUSTFNAME VARCHAR (15),
     CUSTLNAME VARCHAR (15),
     CUSTDOB DATE,
     CUSTPHONEDAY NUMBER (15),
     CUSTPHONEEVE NUMBER (15),
     CONSTRAINT PK_CUST PRIMARY KEY (CUSTNO),
     CONSTRAINT FK_PROF FOREIGN KEY (PROFNO) REFERENCES PROFICIENCY(PROFNO)
     ON DELETE CASCADE);



CREATE TABLE COURSENAME 
    ( COURSENAMENO NUMBER (4) NOT NULL,
      COURSENAME VARCHAR (20),
      COURSEDESC VARCHAR (120),
      COURSEDAYCOST NUMBER (7,2),
      CONSTRAINT PK_COURSENAME PRIMARY KEY (COURSENAMENO));   


CREATE TABLE COURSE
     (COURNO NUMBER (4) NOT NULL, -- creation of primary-key
      COURSTART DATE,
      COUREND DATE,
      COURSENAMENO NUMBER (4) NOT NULL,
      ACCDAYNO NUMBER (4) NOT NULL,
      FOODNO NUMBER (4) NOT NULL,
      TRANSNO NUMBER (4) NOT NULL, 
      CONSTRAINT PK_COURSE PRIMARY KEY (COURNO),
      CONSTRAINT FK_COURSENAME FOREIGN KEY (COURSENAMENO) REFERENCES COURSENAME(COURSENAMENO));

I am researching but I cannot figure out what is happening !!!

score 2 · Answer 1 · edited Jul 29 '20 at 18:25

You need to specify from which table the columns in SELECT and WHERE statements should be retrieved:

select booking.bookno, booking.courno, course.coursename
from booking, course, coursename
where booking.bookno = 6200
and booking.courno = course.courno
and coursename.coursenameno = course.coursenameno

Also, consider using ANSI SQL-92+ JOIN syntax like so:

select booking.bookno, booking.courno, course.coursename
from booking
inner join course on booking.courno = course.courno
inner join coursename on coursename.coursenameno = course.coursenameno
where booking.bookno = 6200

See [Bad habits to kick : using old-style JOINs][1] for some reasoning about it. [1]: https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

good to point out the old join style! Upvote! – Mad Dog Tannen Nov 21 '13 at 14:35 — Mad Dog Tannen, Nov 21 '13 at 14:35
jpw !!! thank you !!! super !!! – user3001481 Nov 21 '13 at 15:02 — user3001481, Nov 21 '13 at 15:02

score 2 · Accepted Answer · answered Nov 21 '13 at 14:34

when the same column appears in several tables you need to specify which table is the one to be used. As a general rulem its always a good idea to prefix the column with the table (or alias) as improves readability and speeds up parsing. so, for your query try (changes in upper case)

select BOOKING.bookno,BOOKING.courno,COURSENAME.coursename
from booking, course,coursename
where BOOKING.bookno = 6200
and booking.courno = course.courno
and coursename.coursenameno = course.coursenameno

thank you very much !!! I spent 5 hours today trying to resolve this !!!! I am so happy !!!! and you guys help me !!!! thank you !! — user3001481, Nov 21 '13 at 14:49

score 1 · Answer 3 · answered Nov 21 '13 at 14:32

When a column is ambigious, this means the database doesnt know which column to use from 2 or more different tables.

You must define in the select like this

select tablename.bookno,tablename.courno,tablename.coursename
from booking, course,coursename
where tablename.bookno = 6200
and booking.courno = course.courno                <-- Here its correct
and coursename.coursenameno = course.coursenameno <-- Here its correct

Change tablename. to the correct table where the column is.

Kay, Super thank you !!! – user3001481 Nov 21 '13 at 15:00 — user3001481, Nov 21 '13 at 15:00

score 0 · Answer 4 · answered Nov 21 '13 at 14:32

0

field courno in your select: you haven't defined from which table: course or booking

answered Nov 21 '13 at 14:32

Copilot

782
6
17

ORA-00918: column ambiguously defined

4 Answers4