51

I get an ambiguous column name error with this query (InvoiceID). I can't figure out why. They all seem to be joined correctly so why doesn't SSMS know to display VendorID?

Query:

SELECT 
    VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM Vendors 
JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
WHERE  
    Invoices.InvoiceID IN
        (SELECT InvoiceSequence 
         FROM InvoiceLineItems
         WHERE InvoiceSequence > 1)
ORDER BY 
    VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
jaielob
  • 531
  • 1
  • 6
  • 9

11 Answers11

73

We face this error when we are selecting data from more than one tables by joining tables and at least one of the selected columns (it will also happen when use * to select all columns) exist with same name in more than one tables (our selected/joined tables). In that case we must have to specify from which table we are selecting out column.

Following is a an example solution implementation of concept explained above

I think you have ambiguity only in InvoiceID that exists both in InvoiceLineItems and Invoices Other fields seem distinct. So try This

I just replace InvoiceID with Invoices.InvoiceID

   SELECT 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
    FROM Vendors 
    JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
    JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
    WHERE  
        Invoices.InvoiceID IN
            (SELECT InvoiceSequence 
             FROM InvoiceLineItems
             WHERE InvoiceSequence > 1)
    ORDER BY 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount

You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers

Sami
  • 8,168
  • 9
  • 66
  • 99
  • Yes and thank you. I didn't want to use an alias because i wanted the column name to remain InvoiceID. – jaielob Sep 30 '12 at 16:49
  • You are welcome. I did not use alias just for I felt you are not feeling convenient with alias – Sami Sep 30 '12 at 16:53
11

You have a column InvoiceID in the Invoices table and also in the InvoiceLineItems table. There is no way for the query execution engine to know which one you want returned.

Adding a table alias will help:

SELECT V.VendorName, I.InvoiceID, IL.InvoiceSequence, IL.InvoiceLineItemAmount
FROM Vendors V
JOIN Invoices I ON (...)
JOIN InvoiceLineItems IL ON (...)
WHERE ...
ORDER BY V.VendorName, I.InvoiceID, IL.InvoiceSequence, IL.InvoiceLineItemAmount
Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
7

Because you are joining two tables Invoices and InvoiceLineItems that both contain InvoiceID. change to Invoices.InvoiceID to make it correct.

Uchiha_Sasuke
  • 309
  • 2
  • 9
4

Most likely both tables have a column with the same name. Alias each table, and call each column with the table alias.

dotancohen
  • 30,064
  • 36
  • 138
  • 197
4

it's because some of the fields (specifically InvoiceID on the Invoices table and on the InvoiceLineItems) are present on both table. The way to answer of question is to add an ALIAS on it.

SELECT 
    a.VendorName,  Invoices.InvoiceID, .. -- or use full tableName
FROM Vendors a   -- This is an `ALIAS` of table Vendors
JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
WHERE  
    Invoices.InvoiceID IN
        (SELECT InvoiceSequence 
         FROM InvoiceLineItems
         WHERE InvoiceSequence > 1)
ORDER BY 
    VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • I was going to edit your answer but mine was too different so I posted cancelled editing and posted answer. It was bit late perhaps. `I want a little guidance from you please`.. For what reason this question got so much votes? Asking because I would follow it while asking question next time. – Sami Sep 30 '12 at 17:02
  • @sami maybe the question is clear. and the OP includes enough information regarding the question.. – John Woo Sep 30 '12 at 17:07
  • @Sami - The voting on stack overflow is somewhat capricious and unpredictable. Impossible to know why this question got 5 upvotes. There are probably plenty of similar questions that also got downvotes (after all if the OP had checked any of the related questions links they would have got their answer) – Martin Smith Sep 30 '12 at 17:12
  • Thanks @MartinSmith Off course. That was my point that this question could be easily searched and even while writing these words in title "ambiguous column name" must have shown similar questions already answered. I got good point from John, however I do not much agree to vote such a question even with very clear contents. Until the question seems bit worthy or special. – Sami Sep 30 '12 at 17:19
4

If you join 2 or more tables and they have similar names for their columns SQL server wants you to qualify columns to which they belong.

SELECT  ev.[ID]
    ,[Description]
    FROM   [Events] as ev 
    LEFT JOIN  [Units] as un ON ev.UnitID = un.UnitId

if Events and Units tables have the same column name (ID) SQL server wants you to use aliases.

Ahmet Arslan
  • 5,380
  • 2
  • 33
  • 35
2

It doesn't just happen in queries with joins.

It can happen when you use ORDER BY on a single table query with a column name that appears twice in the query.

Eg.

SELECT firstname, * FROM person ORDER BY firstname;`

Because firstname appears twice in the result set it is ambiguous which one you want to sort by (even though they are both the same).

You can solve it by using any of these aliases


SELECT firstname AS fn, * FROM person ORDER BY firstname;

SELECT p.firstname, * FROM person p ORDER BY firstname;

-- Its not really clear to me why this next one works but it does
SELECT p.firstname, p.* FROM person p ORDER BY p.firstname;
Dave Pile
  • 5,559
  • 3
  • 34
  • 49
0

One of your tables has the same column name's which brings a confusion in the query as to which columns of the tables are you referring to. Copy this code and run it.

SELECT 
    v.VendorName, i.InvoiceID, iL.InvoiceSequence, iL.InvoiceLineItemAmount
FROM Vendors AS v
JOIN Invoices AS i ON (v.VendorID = .VendorID)
JOIN InvoiceLineItems AS iL ON (i.InvoiceID = iL.InvoiceID)
WHERE  
    I.InvoiceID IN
        (SELECT iL.InvoiceSequence 
         FROM InvoiceLineItems
         WHERE iL.InvoiceSequence > 1)
ORDER BY 
    V.VendorName, i.InvoiceID, iL.InvoiceSequence, iL.InvoiceLineItemAmount
Nelson Katale
  • 1,309
  • 15
  • 21
0

This happens because there are fields with the same name in more than one table, in the query, because of the joins, so you should reference the fields differently, giving names (aliases) to the tables.

Junior Placido
  • 31
  • 2
0

At times you may want to join two tables in SQL and there are in the tables, columns with the same name.

Ambiguous error means that you are calling a certain field in which exist in both Table and the SQL has no idea where to get it. Table 1 has a field (column) name “ID” Table 2 has a field (column) name “ID” as well

Example

SELECT [ID],[Name],[GenderId] FROM [dbo].[TblPerson] AS A
INNER JOIN [dbo].[TblPerson] AS B 
ON A.ID=B.GenderId;

Query Must be

SELECT A.[ID],A.[Name],A.[GenderId] FROM [dbo].[TblPerson] AS A
INNER JOIN [dbo].[TblPerson] AS B 
ON A.ID=B.GenderId;
Ragab Mohamad
  • 371
  • 2
  • 4
0

It outputs (error) ambiguous column name because it gets confused about where to fetch data from since you might have the same query name "InvoiceID" in two different tables or datasets (check all the tables you have used in where clause, InvoiceID should be in at least two of them). To correct this kind of error, you should always specify the query with its tables. Since you are extracting this data from vendors, specify it as "vendors.InvoiceID". To this for all other queries even though it doesn't give you an error.

aditya14581
  • 1