CODEWITHSUNDEEP
sqloracle-database
Bala
Bala

Reputation: 23

error "column ambiguously defined" with column

Below is the SQL join query to list all the records of the columns: dname,ename and location. when I include the column "location", it's throwing an error: "column ambiguously defined"

SQL> select dname,ename,location from emp e join dept d on d.deptno=e.deptno;

Upvotes: 2

Views: 170

Answers (3)

tran mr
tran mr

Reputation: 1

you must specify a table name and field specifier if you have using the data table 2 with the same column name ex: "select p.productName from products p inner join orders o on p.proID = o.proID" list column of products table is: proID, proName, proPrice and list column of orders table is: proID, oid, proName, proPrice. i hope this help to description to you!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Whenever you have more than one table in a query, qualify all column references! This is just a good habit that prevents problems.

When doing so, use table aliases that are abbreviations for the tables being referenced. So, I'm guessing this is what your query intends:

select d.dname, e.ename, e.location
from emp e join
     dept d
     on d.deptno = e.deptno;

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Because of location is present in tables (emp, dept) so you need table alise (e or d) to explicit define the columns (d.dname, e.ename, e.location) where it comes from.

select d.dname, e.ename, e.location 
from emp e join 
     dept d 
     on d.deptno = e.deptno;

Upvotes: 4

Related Questions