Query Error with Ambiguous Column Name in SQL

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 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 resolve Ambiguous column name error SQL

You have [area] in both the union area='000003' and you have it again in the select distinct area from xyzfirms201701. Which one do you actually want to use as "area"? Given that you appear to only want '000003' in the final result then use t0.area (see. second line below).

If you use a cross join in the cte, I presume you want all rows of that returned, so use a left join instead of inner join (? note, I am guessing this)

You are currently grouping by t2.area but don't include in the select clause. Either omit it from the grouping or include in in the select clause. Note because [area] it is part of the join it can only be whatever value you put into the CTE so I suggest you use sizeclasseptable.area

;with sizeclasseptable as (
    select t0.area,ownership,sizeclassep 
    from (
                  select '01' as sizeclassep, '50' as ownership, area='000003'
        union all select '02' as sizeclassep, '50' as ownership, area='000003'
        union all select '03' as sizeclassep, '50' as ownership, area='000003'
        union all select '04' as sizeclassep, '50' as ownership, area='000003'
        union all select '05' as sizeclassep, '50' as ownership, area='000003'
        union all select '06' as sizeclassep, '50' as ownership, area='000003'
        union all select '07' as sizeclassep, '50' as ownership, area='000003'
        union all select '08' as sizeclassep, '50' as ownership, area='000003'
        union all select '09' as sizeclassep, '50' as ownership, area='000003'
        ) t0
    /* cross join ( select distinct area from xyzfirms201701 ) t1 */
    )

SELECT
    sizeclasseptable.area  AS [area]
  , t2.SizeClassep
  , COUNT(*)               AS [Number of Worksites]
  , SUM(t2.Employment)     AS [Employment In Size Class]
FROM sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.area = sizeclasseptable.area
    AND t2.ownership = sizeclasseptable.ownership
    AND t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    sizeclasseptable.area
  , t2.SizeClassep
ORDER BY
    sizeclasseptable.area
  , t2.SizeClassep

---

edit

An alternative approach:

DECLARE @ownership varchar(20) = '50'
DECLARE @area varchare(20) = '000003'

WITH sizeclasseptable
AS (
    SELECT
        sizeclassep
    FROM (
                  select '01' as sizeclassep
        union all select '02' as sizeclassep
        union all select '03' as sizeclassep
        union all select '04' as sizeclassep
        union all select '05' as sizeclassep
        union all select '06' as sizeclassep
        union all select '07' as sizeclassep
        union all select '08' as sizeclassep
        union all select '09' as sizeclassep
    ) t0
)

SELECT
    t2.area
  , t2.SizeClassep
  , COUNT(*)           AS [Number of Worksites]
  , SUM(t2.Employment) AS [Employment In Size Class]
FROM sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.area = @area
AND t2.ownership = @ownership
AND t2.sizeclassep = sizeclasseptable.sizeclassep
GROUP BY
    t2.area
  , t2.SizeClassep
ORDER BY
    t2.area
  , t2.SizeClassep

edit 2

Perhaps a method to reduce the number of query iterations would be to expand the grouping to all 3 of the columns used to select that data, and also broadening the way the where clause is defined. There is also a different method for produce the SizeClassep rows by using values

SELECT
    t2.area
  , t2.SizeClassep
  , t2.ownership
  , COUNT(*)           AS [Number of Worksites]
  , SUM(t2.Employment) AS [Employment In Size Class]
FROM (
    SELECT
        sizeclassep
    FROM (
    VALUES ('01'), ('01'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09')
    ) t0 (sizeclassep)
) sizeclasseptable
LEFT JOIN xyzfirms201701 t2 ON t2.sizeclassep = sizeclasseptable.sizeclassep
AND t2.area IN ('0003','0004','0005','0006')  /* ALTER THE LIST TO SUIT YOUR NEEDS */
AND t2.ownership IN ('50','60','70')          /* ALTER THE LIST TO SUIT YOUR NEEDS */
AND 
GROUP BY
    t2.area
  , t2.SizeClassep
  , t2.ownership
ORDER BY
    t2.area
  , t2.SizeClassep
  , t2.ownership

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.

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

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.

SQL server ERROR Ambiguous column name

USE [My_db]
GO

SELECT e.ItemId
      ,Subject
      ,CreatedOn
  FROM ItemBase AS e
      INNER JOIN ItemExtensionBase AS p
      ON e.ItemId = p.ItemId
GO

You need to tell it which table to take the itemid field from

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.

---

Related Topics