Call a column that are locted in two tables

Question

I am trying to get CustomerID and I keep getting an error. Here is my code:

SELECT CustomerID, FirstName, LastName,Line1,Line2, City, State, ZipCode
FROM Customers INNER JOIN Addresses
    ON Customers.CustomerID = Addresses.CustomerID;

My error is:Ambiguous column name 'CustomerID'. How can that be when I used a JOIN statement. any thoughts?

`SELECT Customers.CustomerID..` `CustomerID` column is present in both the tables so you need to the compiler from which table values should be pulled. — Pரதீப், Oct 25 '16 at 15:16
Just specify which table you want it from `SELECT Customers.CustomerID` or `SELECT Addresses.CustomerID` — RB., Oct 25 '16 at 15:16
yes, add the table (or table alias) as a prefix. Which, incidentally, is exactly what you did in the join condition — Lamak, Oct 25 '16 at 15:16
You should learn how to use aliases. But look at your query. How does it know which table to pull the CustomerID value from? You need to specify the table. — Sean Lange, Oct 25 '16 at 15:16

score 1 · Answer 1 · answered Oct 25 '16 at 15:17

1

Use aliases:

SELECT C.CustomerID, C.FirstName, C.LastName, A.Line1, A.Line2, A.City, A.State, A.ZipCode
FROM Customers C
INNER JOIN Addresses A
        ON C.CustomerID = A.CustomerID;

answered Oct 25 '16 at 15:17

HoneyBadger

14,750
3
34
48

This query is cleaner than mine, I should have posted less quickly :-) – Tim Biegeleisen Oct 25 '16 at 15:19

score 0 · Answer 2 · answered Oct 25 '16 at 15:19

There are two columns in your results named "CustomerID", so SQL Server doesn't know which one you mean. It doesn't matter that you've specified that they are equal in your JOIN. SQL still needs you to specify which one you want.

You can just add the table name to your column, but I prefer table aliases instead:

SELECT
    C.CustomerID,
    C.FirstName,
    C.LastName,
    A.Line1,
    A.Line2,
    A.City,
    A.State,
    A.ZipCode
FROM
    Customers C
INNER JOIN Addresses A ON A.CustomerID = C.CustomerID;

score 0 · Answer 3 · edited Oct 25 '16 at 15:28

You have to tell the Database Engine which costumerID to display from the join.

SELECT **Addresses.CustomerID**, FirstName, LastName,Line1,Line2, City, State, ZipCode 
FROM Customers 
INNER JOIN Addresses ON Customers.CustomerID = Addresses.CustomerID

or

SELECT **Customers.CustomerID**, FirstName, LastName,Line1,Line2, City, State, ZipCode 
FROM Customers 
INNER JOIN Addresses ON Customers.CustomerID = Addresses.CustomerID

Call a column that are locted in two tables

3 Answers3