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
9 Answers
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
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
Because you are joining two tables Invoices and InvoiceLineItems that both contain InvoiceID. change to Invoices.InvoiceID to make it correct.
Most likely both tables have a column with the same name. Alias each table, and call each column with the table alias.
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
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.
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;
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
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.
