Query error with ambiguous column name in SQL

Question

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  gt  1  ORDER BY      VendorName  InvoiceID  InvoiceSequence  InvoiceLineItemAmount

User · Answer

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  gt  1  ORDER BY      V VendorName  i InvoiceID  iL InvoiceSequence  iL InvoiceLineItemAmount

User · 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  gt  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

User · Answer

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  gt  1  ORDER BY      VendorName  InvoiceID  InvoiceSequence  InvoiceLineItemAmount

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Query error with ambiguous column name in SQL

Examples related to sql