CODEWITHSUNDEEP
mysql
Sofi
Sofi

Reputation: 41

MySql query column in field list is ambiguous

I have OrderTbl table with OrdNo, OrdDate, OrdState, CustNo and other columns and Customer table with CustNo, CustFirstName, CustState and other columns. And I need to select all of them but when I am writing a query 

  select OrdNo, OrdDate,  OrdState, CustFirstName, CustState, CustNo from OrderTbl,Customer

MySQl gives me an error than mu CustNo column is ambiguous and I understand that it because CustNo in both tables..But what can I do then?

Upvotes: 2

Views: 2561

Answers (1)

Psi
Psi

Reputation: 6783

You are using two tables, which both have a column called CustNo. How should MySQL know what column you want?

Also, you would want to join the tables correctly:

select o.OrdNo, o.OrdDate,  o.OrdState, c.CustFirstName, c.CustState, c.CustNo 
from 
     OrderTbl o
join Customer c
on 
     o.CustNo = c.CustNo

What you need to do, is using table aliases or explicitly addressing the columns you want to read.

-- alternative to the query above without aliases
select OrderTbl.OrdNo, OrderTbl.OrdDate,  OrderTbl.OrdState, Customer.CustFirstName, Customer.CustState, Customer.CustNo 
from 
     OrderTbl
join Customer
on 
     OrderTbl.CustNo = Customer.CustNo

Upvotes: 1

Related Questions