Ошибка sql ambiguous column name

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

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

answered Sep 30, 2012 at 16:45

SamiSami

8,0548 gold badges66 silver badges97 bronze badges

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

answered Sep 30, 2012 at 16:38

Graeme PerrowGraeme Perrow

55.2k20 gold badges79 silver badges121 bronze badges

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

29.2k35 gold badges136 silver badges194 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 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

answered Sep 30, 2012 at 16:39

John WooJohn Woo

255k69 gold badges492 silver badges488 bronze badges

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

4,9621 gold badge31 silver badges34 bronze badges

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;

answered Aug 27, 2021 at 0:40

Dave PileDave Pile

5,3553 gold badges33 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.

answered Aug 16, 2022 at 8:39

Источник

In SQL, ambiguous column error you can see many times. Here’re a few examples of how to resolve this error. Here is a link for SQL set operators 10 Top Rules for SQL Set Operators.

The ambiguous columns mean, SQL does not understand which column it has to consider.

Let me explain in detail. In the classroom, there are two boys with the same name…

Okay.

If a teacher calls the name, both boys look up their heads. So, it is confusing to the teacher and the classroom…..students who are sitting inside.

So, the teacher made some changes…

Added some uniqueness to each boy’s name…

Then, the problem is resolved.

How to Resolve Ambiguous column error?

The fix or resolution for the ambiguous column is as follows:

SELECT invoice_numb, 
       vendor_name 
FROM   vendors 
       INNER JOIN invoices 
               ON vendor_id = vendor_id 
ORDER  BY invoice_numb;

In the above query, the incorrectness is you are joining on vendor_id. The problem is vendor_id is present in both the tables. This is called ambiguous.

 SELECT invoice_numb, 
       vendor_name 
FROM   vendors 
       INNER JOIN invoices 
               ON vendors.vendor_id = invoices.vendor_id 
ORDER  BY invoice_numb;

According to BURLESON, A column name used in a join exists in more than one table and is thus referenced ambiguously.

How I fixed?

In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced.

The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO. Read his example here.

According to Oracle documents.

ORA-00918 column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously.
In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN.
For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

Also read,

32 Tricky SQL queries you need for all interviews

Please subscribe to “Srinimf Show” and rate and review podcasts:

Soundcloud/srinimf-show

Follow me on Social Media:

Twitter
Facebook
Instagram

Источник

Когда вы объединяете несколько таблиц в SQL-запросе, если столбец с одинаковым именем присутствует в обеих таблицах, то BigQuery не знает, какой из них использовать (если вы явно не скажете об этом), поэтому он выдает ошибку с неоднозначным именем столбца.

Редактор Bigquery WebUI достаточно умен, чтобы выделить точную строку, в которой присутствует неоднозначный столбец (обратите внимание на красное восклицание на полях редактора sql). Решение может быть в одном из приведенных ниже методов:

Убедитесь, что в списке выбора нет неоднозначных имен столбцов:

select Id, Name, Description
from table1 t1 
join table2 t2
on t2.Id = t1.Id;

В этом примере Идентификатор имени столбца присутствует в обеих таблицах, t1 и t2. Вы можете прояснить это, разметив столбцы, как показано ниже:

select t1.Id, t1.Name, t2.Description
from table1 t1 
join table2 t2
on t2.Id = t1.Id;

Убедитесь, что предложение Where / Join не имеет двусмысленных имен столбцов:

select t1.Id, t1.Name, t2.Description
from table1 t1 
join table2 t2
 on t2.Id = t1.Id
Where Id = 100;

В этом примере, поскольку идентификатор присутствует в обеих таблицах, создайте псевдоним столбца, как показано ниже:

select t1.Id, t1.Name, t2.Description
from table1 t1 
join table2 t2
 on t2.Id = t1.Id
Where t1.Id = 100;

Никогда не используйте запятые в предложении FROM. Всегда используйте правильный, явный синтаксис Join:

select table1.* 
from table1,
 table2,
 table3
where table1.Id = table2.Id;

Использование запятых для Join(соединений) может сбивать с толку, приводить к ошибкам и данный метод имеет меньшую читабельность. Всегда старайтесь использовать явное объединение и явные столбцы объединения. Это значительно сократит количество ошибок, а также упростит работу по устранению неполадок

Источник

Remove From My Forums

Question
I am working on my first table join, so I might have a couple of errors. Let’s start with the column «Status». This query works:

select * from tblCsCases
where [Status] = ‘REVIEW’

However, this query does not work:

select MAX(FileNumber) as FileNumber, MAX(PaStartDate) as PaStartDate, A.LastName, COUNT(A.LastName) as Attorneys

from tblCsCases E INNER JOIN tblCtAttorney A
on E.ProsecutingAttorney = A.BarCode
where [Status] = ‘REVIEW’
and PaStartDate BETWEEN ‘20100101’ AND ‘20161231’
group by A.LastName

I am getting the following error:

Msg 209, Level 16, State 1, Line 4
Ambiguous column name ‘Status’.

Any idea what is the problem. If you need more information, please let me know. Thank you for your help.

Kurt

Answers

Just prepend with the table alias, e.g. E.[Status]
- Marked as answer by
  
  Monday, September 12, 2016 4:52 AM

Источник

Home > SQL Server Error Messages > Msg 209 — Ambiguous column name ‘<Column Name>‘.

SQL Server Error Messages — Msg 209 — Ambiguous column name ‘<Column Name>‘.

SQL Server Error Messages — Msg 209

Error Message

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name '<Column Name>'.

Causes:

This error happens when you are joining 2 or more tables in a statement and there are columns among these tables that have the same name and you didn’t prefix the column name with the table name when referencing the column in your statement.

To illustrate, let’s say you have the following tables which contains the Employees and Supervisors of your company:

CREATE TABLE [dbo].[Supervisors] (
    [SupervisorID]      INT,
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50)
)

CREATE TABLE [dbo].[Employees] (
    [EmployeeID]        INT,
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50),
    [SupervisorID]      INT
)

You want to generate a list of your employees together with the name of their corresponding supervisors. In doing so, you issued the following SELECT statement:

SELECT [FirstName] AS [EmployeeFirstName],
       [LastName]  AS [EmployeeLastName], 
       [FirstName] AS [SupervisorFirstName],
       [LastName]  AS [SupervisorLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Supervisors] Sup
ON Emp.[SupervisorID] = Sup.[SupervisorID]

Issuing this SELECT statement in Query Analyzer will generate the following errors:

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FirstName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LastName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FirstName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LastName'.

Solution / Work Around:

To avoid this error, make sure that you prefix the column name with the table name or table alias for those columns that exist in more than 1 table. Here’s an updated SELECT statement that will not generate the error:

SELECT Emp.[FirstName] AS [EmployeeFirstName],
       Emp.[LastName]  AS [EmployeeLastName], 
       Sup.[FirstName] AS [SupervisorFirstName],
       Sup.[LastName]  AS [SupervisorLastName]
FROM [dbo].[Employees] Emp INNER JOIN [dbo].[Supervisors] Sup
ON Emp.[SupervisorID] = Sup.[SupervisorID]

It is a good practice to always prefix column names with the table name or table alias so that in case additional columns are added to a table that have the same name as existing columns in another table, you won’t encounter this error.

Frequently Asked Questions — SQL Server Error Messages
Frequently Asked Questions — INSERT Statement
Frequently Asked Questions — SELECT Statement

Источник

Ошибка sql ambiguous column name

How to Resolve Ambiguous column error?

How I fixed?

Убедитесь, что в списке выбора нет неоднозначных имен столбцов:

Убедитесь, что предложение Where / Join не имеет двусмысленных имен столбцов:

Никогда не используйте запятые в предложении FROM. Всегда используйте правильный, явный синтаксис Join:

Question

Answers

А вот еще интересные материалы: