Ambiguous Column Name Error

Query error with ambiguous column name in SQL

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

Error: Ambiguous column name

you use the aho2 table alias twice change one of the references to something else

it looks like you are doing string conactenation of rows to a semi colon delimited string. But if you look in your second stuff statement you use the table alias aho2 and then you use it again in the last table reference. So one of the 2 references need to change otherwise sql-server doesn't know which one you are referencing.

But now that I look deeper you also have an issue in your second select statement that you have a cross join specified due to implicit join sytax and specifying the table twice. My guess is you don't want that either here is one way (a guess) that might get you want you want but if not you should update your question with schema, example data, and desired result so that we can more effectively assist you.

SELECT 
    aho3.[Control Number] AS [Control Number]
    ,STUFF(
       (SELECT '; '+[Old Value] as [text()]
       FROM #AuditHistoryOutput aho1
       WHERE [aho1].[Control Number] = aho3.[Control Number]
       FOR XML PATH(''))
       , 1, 1, '') [Unset Choice Value]
    ,STUFF(
       (SELECT '; '+[New Value] as [text()]
       FROM #AuditHistoryOutput aho2
       WHERE [aho2].[Control Number] = aho3.[Control Number]
       FOR XML PATH(''))
       , 1, 1, '') [Set Choice Value]
FROM #AuditHistoryOutput aho3

error with a sql query because of ambiguous column name

Both tables coming into play in the query have a column named description. You RDBMS cannot guess which column table you actually want.

You need to prefix the column name with the table name (or table alias) to disambiguate it.

Bottom line, it is a good practice to always prefix column names with table names or aliases as soon as several tables come into play in a query. This avoids the issue that you are seeing here and make the queries easier to understand for the poor souls that have no knowledge of the underlying schema.

Here is an updated version of your query with table aliases and column prefixes. Obviously you need to review each column to put the correct alias:

SELECT TOP 1000 
       i.[activityid]
      ,i.[activitytypecodename]
      ,i.[subject]
      ,c.[regardingobjectid]
      ,c.[contactid] 
      ,c.[new_crmid]
      ,c.[description] AS description_pointer
FROM [crmtestext_MSCRM].[dbo].[FilteredActivityPointer] as i
   Left JOIN  [crmtestext_MSCRM].[dbo].[FilteredContact] as c
  ON i.[regardingobjectid] = c.[contactid]
  WHERE i.new_crmid not like '%Null%' AND i.activitytypecodename like '%E-mail%'

How to solve ambiguous column name in SQL server?

Because of your need to tell SQL server what EmailAddress want to get from tables on SELECT part, there are two kind of EmailAddress from your query.

select R.EmailAddress, [Order Number], order_date, rank,
ROW_NUMBER() OVER (PARTITION BY order_date, R.EmailAddress ORDER BY order_date DESC) AS rankDate,
R.firstname as 'FirstName',R.lastname as 'LastName', 'IL' as 'Locale'
from( 

    select
    O.FirstName, O.LastName, O.email as 'EmailAddress',
    O.order_number as 'Order Number',O.order_date,
    ROW_NUMBER() OVER (PARTITION BY O.email ORDER BY O.order_date DESC) AS rank,
    O.order_number as 'Order Number Compare' 
    from [TEST ORDER] O
    where O.order_date>='12/27/2020' and O.email is not null 
) as R
join [All Blend Subscribers] as ABS
on R.EmailAddress = ABS.emailAddress

I would get columns by an alias clearly because it can avoid ambiguous error.

SqlAlchemy SQL Server Ambiguous Column Name Error

the problem was because of the SqlAlchemy version.The version in my local machine was 1.4.25 when I install newest version to any where I got the sql error shown above.So this problem was happened because of the version.Thanks.

Ambiguous column name error SQL

You are selecting just ClaimId in your SELECT statement, but you have multiple tables with ClaimId in it.

You need to tell it which table you're pulling from.

Based on your join:

fcbscc.ClaimID = fcay.ClaimID

Doing either

SELECT fcbscc.ClaimID 

or

SELECT fcay.ClaimID

will suffice

SQL error: Ambiguous column name

This means that SALESYTD is in both tables. I don't know which you want.

When you have more than one table in a query always qualify your column names.

SELECT ST.NAME, ST.COUNTRYREGIONCODE,
       AVG(SP.SALESQUOTA), AVG(SP.BONUS), AVG(SP.SALESYTD)  
FROM SALES.SALESPERSON SP INNER JOIN
     SALES.SALESTERRITORY ST
     ON SP.TERRITORYID = ST.TERRITORYID
GROUP BY ST.NAME, ST.COUNTRYREGIONCODE; 

I'm just guessing where the columns come from.

SQL error Ambiguous column name

There probably isn't any reason to count a column name. Just count all the rows using *:

SELECT COUNT_BIG(*) AS [Antall ordre] 
FROM dbo.[3S Company A_S$Warehouse Activity Header] ah NNER JOIN
     dbo.[3S Company A_S$Sales Header] sh
     ON sh.[No_] = ah.[Source No_]  
WHERE ah.[Destination No_] = '" & strSelskab & "'  and
      ah.[No_ Printed] > 0

Notice the use of table aliases. This also makes the query easier to write and to read.

Ambiguous column name 'Created_Date'?

You have:

CASE WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 90 AND 119 

But the error message is clear: more than one table in your query has a column named Created_Date. So, you need to use the table aliases you created to tell SQL Server which one you mean. Presumably it is from dbo.invoices, so:

CASE WHEN DATEDIFF(day, in.[Created_Date], Getdate()) Between 90 AND 119 

Though in is also a poor name choice for an alias because, like double, it is a reserved word/keyword and at the very least will light up in various editors or, in some contexts, cause a syntax error.

More importantly:

DATEDIFF(day, in.[Created_Date], Getdate()) >= 365 

This is much more efficiently written as:

in.Created_Date < DATEADD(DAY, -365, CONVERT(date, GETDATE())

It's a few extra characters, I know, but that at least has a shot of using an index.

Also, if you traverse the opposite way (oldest category to newest), you don't have to handle the BETWEEN scenario at all, because the first scenario that matches will render the rest of the comparisons no-ops:

CASE
WHEN in.Created_Date < DATEADD(DAY, -365, CONVERT(date, GETDATE())
  THEN '365+ Days'
WHEN in.Created_Date < DATEADD(DAY, -180, CONVERT(date, GETDATE())
  THEN '180 Days'
WHEN in.Created_Date < DATEADD(DAY, -120, CONVERT(date, GETDATE())
  THEN '120 Days'
WHEN in.Created_Date < DATEADD(DAY, -90,  CONVERT(date, GETDATE())
  THEN '90 Days'
END AS [Days Outstanding]

You could also simplify it by:

DECLARE @today date = GETDATE();

CASE
WHEN in.Created_Date < DATEADD(DAY, -365, @today)
  THEN '365+ Days'
...

---

Related Topics