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
asked Sep 30, 2012 at 16:35
0
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
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
0 that exists both in
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
1 and
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
2 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
answered Sep 30, 2012 at 16:45
SamiSami
8,1389 gold badges67 silver badges99 bronze badges
2
You have a column
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
0 in the
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
2 table and also in the
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
1 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
answered Sep 30, 2012 at 16:38
Graeme PerrowGraeme Perrow
56.4k22 gold badges83 silver badges123 bronze badges
0
Because you are joining two tables Invoices and InvoiceLineItems that both contain InvoiceID. change to Invoices.InvoiceID to make it correct.
answered Sep 30, 2012 at 16:40
Most likely both tables have a column with the same name. Alias each table, and call each column with the table alias.
answered Sep 30, 2012 at 16:39
dotancohendotancohen
30.4k39 gold badges140 silver badges197 bronze badges
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
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
6 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
answered Sep 30, 2012 at 16:39
John WooJohn Woo
260k69 gold badges499 silver badges493 bronze badges
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.
answered Aug 26, 2015 at 7:50
Ahmet ArslanAhmet Arslan
5,5202 gold badges33 silver badges35 bronze badges
1
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
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
7 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;
answered Aug 27, 2021 at 0:40
Dave PileDave Pile
5,6243 gold badges34 silver badges49 bronze badges
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
answered Nov 29, 2018 at 10:01
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.
answered Dec 19, 2018 at 11:18
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;
answered Jul 23, 2022 at 7:56
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.