[sql] Query error with ambiguous column name in SQL

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 the management studio know to display VendorID? Any help would be greatly appreciated.

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

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

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.

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

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

if you join 2 or more tables and they have similar names for their columns sql server wants you to qualify columns 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 has same column name (ID) SQL server wants you to use aliases.

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.