ORA 00918- Column ambiguosly defined error

Question

There are two tables in my Oracle database.

First table is (customers)-

customer_id Customer_name Customer_age Customer_address salary
103 Giriraj Rathi      22   Kolkata    12000
100 Subir Adhikari     22   Bolpur     10000
101 Rakesh Chatterjee  21   Tarkeshwar 8000
102 Jayanta Patra      20   Tarkeshwar 9000
104 Abhi Karmakar      22   Burdwan    8000
105 Mainak Manna       21   Burdwan    9000
106 Subho Gupta        20   Kolkata    10000
107 Aritra Das         23   Kolkata    7000
108 Pradip Paul        22   Kolkata    5000
109 Sourav Banerjee    22   Bolpur     9000

Second table is (Orders):

Order_id Order_date   customer_id  amount

200 12-03-13    100 1100
201 09-05-13    101 1400
202 07-04-12    103 2500
204 29-05-13    104 2400
203 09-02-13    105 9000
205 18-06-13    106 2100
206 09-07-13    107 1600
207 18-05-13    108 2900
209 18-04-13    109 2400

Now I wanted to join both the tables. So I used the query:

select customer_id,
       customer_name,
       customer_address, 
       order_id,order_date, 
       amount 
  from customers, 
       orders 
 where customers.customer_id=orders.customer_id;

I Googled about the error and found this happens when there is ambiguity in the SQL code itself, but in this case I see nothing.

I don't how good your Google Fu is but you didn't type that error number into the StackOverflow search box. Otherwise you would have found dozens of threads on this, almost any one of which would have answered your question. http://stackoverflow.com/search?q=ORA+00918 — APC, Sep 22 '13 at 08:23
@APC, The question you linked to is a little bit more complicated than my question whereas the error in the code/concept is too basic to catch; so I insist this question should not be closed, rather according to me it is the simplest question where the future visitors would catch their error easily. — Mistu4u, Sep 22 '13 at 16:18
Too many questions on the same topic just create noise. There are sixteen questions tagged [ora-00918] and almost another fifty questions mentioning that error message. — APC, Sep 22 '13 at 17:25

score 2 · Accepted Answer · answered Sep 22 '13 at 06:22

2

It is always a good idea to add the table name/alias to the column like this

select c.customer_id,
       c.customer_name, 
       c.customer_address, 
       o.order_id,
       o.order_date,
       o.amount 
from customers c
inner join orders o on c.customer_id = o.customer_id

If you don't then the DB don't know which column to take and both tables have a column named customer_id.

answered Sep 22 '13 at 06:22

juergen d

201,996
37
293
362

Needless to say, this solved the problem. But I am curious to know where the error can be? – Mistu4u Sep 22 '13 at 06:25
Please note that I have used table names in my code, `where customers.customer_id=orders.customer_id;` where `customers` and `orders` are the tables. – Mistu4u Sep 22 '13 at 06:27
Like I said. Both tables have a column with the same name. `customer_id`. When you say `select customer_id ...` what should the DB take? – juergen d Sep 22 '13 at 06:27
but not in your select clause – juergen d Sep 22 '13 at 06:27
Okay, Now I understood :) – Mistu4u Sep 22 '13 at 06:27
@Mistu4u Alternatively, this syntax `customers JOIN orders USING (customer_id)` lets Oracle know that customer_id from both tables has the same value. So you get a unique column named customer_id in your result set and you no longer have to had the table name. – Nicolas Sep 22 '13 at 16:07

ORA 00918- Column ambiguosly defined error

1 Answers1

Linked

Related