Query error with 2 ambiguous column names in SQL

E. Peterson picture E. Peterson · Mar 16, 2015 · Viewed 10.4k times · Source

I've been working with this Query for bit now and I'm having a hard time. I'm new to SQL and I can't figure our why I'm getting the error:

SELECT customer_number, first_name_initial, last_name,serve_address_1, serve_address_2, serve_city, serve_state, route_serve_zip_code, phone_number1, referral_code
FROM customer_master 
INNER JOIN route_detail 
ON m.customer_number=r.customer_number
WHERE (referral_code='american')

ERROR

Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'customer_number'. Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'phone_number1'.

I've tried appending the columns as follows but no luck with that either. Any help would be greatly appreciated!

SELECT customer_number, first_name_initial, last_name,serve_address_1, serve_address_2, serve_city, serve_state, route_serve_zip_code, phone_number1, referral_code
FROM customer_master m
INNER JOIN route_detail r
ON m.customer_number=r.customer_number
WHERE (referral_code='american')

Answer

Rahul Tripathi picture Rahul Tripathi · Mar 16, 2015

The problem is with this line

SELECT customer_number

You need to specify from which table you want to fetch the customer_number like this:

SELECT r.customer_number

or

SELECT m.customer_number

So your final query would look like this:

SELECT r.customer_number, --or m.customer_number
first_name_initial, last_name,serve_address_1, serve_address_2, serve_city, serve_state, route_serve_zip_code, phone_number1, referral_code
FROM customer_master 
INNER JOIN route_detail 
ON m.customer_number=r.customer_number
WHERE (referral_code='american')