SQL Server 2012 Multi part identifier could not be bound

2
votes

I have 4 tables I'm joining in Adventureworks 2012. I cant figure out why I'm getting the 'could not be bound error'.

Msg 4104, Level 16, State 1, Line 7

The multi-part identifier "Production.ProductCategory.Name" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "Production.ProductCategory.Name" could not be bound.

SELECT
    COUNT(WorkOrderID) AS TotalWorkOrders, 
    [Production].[ProductCategory].[Name]
FROM [Production].[WorkOrder] WO
INNER JOIN [Production].[Product] P ON WO.[ProductID] = P.[ProductID]
INNER JOIN [Production].[ProductSubcategory] PS ON PS.[ProductSubcategoryID] = P.[ProductSubcategoryID]
INNER JOIN [Production].[ProductCategory] PC ON PC.[ProductCategoryID] = PS.[ProductCategoryID]
WHERE WO.[StartDate] >= '1999—03-08' AND WO.[StartDate] <= '2008-05-02'
GROUP BY [Production].[ProductCategory].[Name]
4
sql-server

4 Answers

5
votes

Your query has given the table [Production].[ProductCategory] the alias PC. You need to use this in the rest of the query:

SELECT COUNT(WO.WorkOrderID) AS TotalWorkOrders, 
       PC.[Name]
FROM [Production].[WorkOrder] WO
     INNER JOIN [Production].[Product] P ON WO.[ProductID] = P.[ProductID]
     INNER JOIN [Production].[ProductSubcategory] PS ON PS.[ProductSubcategoryID] = P.[ProductSubcategoryID]
     INNER JOIN [Production].[ProductCategory] PC ON PC.[ProductCategoryID] = PS.[ProductCategoryID]
WHERE WO.[StartDate] >= '1999—03-08' AND WO.[StartDate] <= '2008-05-02'
GROUP BY PC.[Name];

Once you give a table an alias, you need to refer to that alias instead of the original table name.

0
votes

You should be using the alias in the column list:

SELECT
    COUNT(WorkOrderID) AS TotalWorkOrders, 
    PC.[Name]
FROM [Production].[WorkOrder] WO
INNER JOIN [Production].[Product] P ON WO.[ProductID] = P.[ProductID]
INNER JOIN [Production].[ProductSubcategory] PS ON PS.[ProductSubcategoryID] = P.[ProductSubcategoryID]
INNER JOIN [Production].[ProductCategory] PC ON PC.[ProductCategoryID] = PS.[ProductCategoryID]
WHERE WO.[StartDate] >= '1999—03-08' AND WO.[StartDate] <= '2008-05-02'
GROUP BY PC.[Name]
0
votes

Sometimes this error occurs when you use your schema ([Production]) in your query in a wrong way.

For example if you write:

select [Production].[ProductCategory].[Name]
from [Production].[WorkOrder] WO

you will get the error.

In this situations change it to:

select [ProductCategory].[Name]
from [Production].[WorkOrder] WO
0
votes

Please write PC.[name] in group byclause..bcoz you given already alias as PC for table product.prooductcategory.hence must use alias PC to define all the colums of product category tables in the query wherever necessary..