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
Advertisement
Answer
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