Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Ambiguous column when make a normal inner join

Answered Ambiguous column when make a normal inner join

  • Tuesday, January 22, 2013 10:50 AM
     
      Has Code
    Sign In to Vote
    0

    Hi everyone, any suggestion about the error below?

    select sum(salesamount),
 CalendarYear, sum(orderquantity), p.EnglishProductName from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
 inner join   dimdate d on d.DateKey=i.DueDateKey inner join DimCustomer o 
 on i.CustomerKey=o.CustomerKey inner join DimGeography g on o.GeographyKey=g.GeographyKey where g.EnglishCountryRegionName='australia' group by CalendarYear, ProductKey order by ProductKey, CalendarYear asc

    Ambiguous column name 'productkey'.

    Without the join with product everything works...

     

All Replies

  • Tuesday, January 22, 2013 10:56 AM
     
     Answered
    Sign In to Vote
    0

    On the group by and order by clause you need to give the correct table alias for  CalendarYear, ProductKey

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked As Answer by DIEGOCTN Tuesday, January 22, 2013 11:04 AM
    •  
     
  • Tuesday, January 22, 2013 10:56 AM
     
     Answered
    Sign In to Vote
    0

    try this

    select sum(salesamount), CalendarYear, sum(orderquantity), p.EnglishProductName 
     from FactInternetSales i inner join DimProduct p on p.ProductKey=i.ProductKey 
     inner join   dimdate d on d.DateKey=i.DueDateKey
     inner join DimCustomer o  on i.CustomerKey=o.CustomerKey 
     inner join DimGeography g on o.GeographyKey=g.GeographyKey 
     where g.EnglishCountryRegionName='australia' 
     group by CalendarYear, p.ProductKey order by p.ProductKey, CalendarYear asc

    If u are using the columnname common for two tables, then its always mandatory to specify alias name when used in group By or Oreder By clause.

    Please have look on the comment


    • Edited by SanthoshH Tuesday, January 22, 2013 10:59 AM
    • Marked As Answer by DIEGOCTN Tuesday, January 22, 2013 11:04 AM
    •