joining to tables in a query for ASP.NET C# ~ multi-part-identifier-could-not-be-bound

Question

Among other answers I have looked at the following:

The multi-part identifier could not be bound

However tying to use that answer I was still not able to make my grouping work.

The following query works:

string query = @"SELECT     
    ud.FirstName, 
    ud.LastName, 
    p.Name AS Product, 
    p.ItemNumber, 
    sc.AmountPurchased * sc.Price AS Total, 
    sc.DatePurchased,
    sc.IsInCart AS [NotShipped]
FROM         
    ShoppingCarts sc INNER JOIN
    UserDetails ud ON sc.ClientID = ud.Guid Left     
OUTER JOIN
    Products p ON sc.ProductID = p.ProductsId

WHERE sc.DatePurchased >= @date1
AND   sc.DatePurchased <= @date2  
AND   sc.IsInCart = @shipped ";

but when I try to group it like this:

string query = @"SELECT ud.LastName, sc.DatePurchased, SUM(Total)
    FROM(
SELECT     
    ud.FirstName, 
    ud.LastName, 
    p.Name AS Product, 
    p.ItemNumber, 
    sc.AmountPurchased * sc.Price AS Total, 
    sc.DatePurchased,
    sc.IsInCart AS [NotShipped]
FROM         
    ShoppingCarts sc INNER JOIN
    UserDetails ud ON sc.ClientID = ud.Guid Left     
OUTER JOIN
    Products p ON sc.ProductID = p.ProductsId
WHERE sc.DatePurchased >= @date1
AND   sc.DatePurchased <= @date2  
AND   sc.IsInCart = @shipped 
) AS result
GROUP BY ud.LastName, sc.DatePurchased";

I get the "multi-part identifier ud.LastName, etc cannot be bound"

The fields are no longer part of the aliased tables in the outer select. Just remove the `ud` and `sc` prefixes in the outer `select` and `group by`. — Johnbot, May 05 '15 at 14:09
well, the error went away but now it's not giving me any data when I know there is data to return... — Diana, May 05 '15 at 14:14
http://stackoverflow.com/questions/206558/what-is-a-multi-part-identifier-and-why-cant-it-be-bound please refer this i think it will help you — , May 05 '15 at 14:29

score 1 · Accepted Answer · answered May 06 '15 at 06:51

When doing nested selects the aliases do not propagate out of their scope. This is more evident when looking at the equivalent CTE query (assuming you're using MS SQL Server / T-SQL):

;WITH
(
    SELECT     
        ud.FirstName, 
        ud.LastName, 
        p.Name AS Product, 
        p.ItemNumber, 
        sc.AmountPurchased * sc.Price AS Total, 
        sc.DatePurchased,
        sc.IsInCart AS [NotShipped]
    FROM         
        ShoppingCarts sc INNER JOIN
        UserDetails ud ON sc.ClientID = ud.Guid Left     
    OUTER JOIN
        Products p ON sc.ProductID = p.ProductsId
    WHERE sc.DatePurchased >= @date1
    AND   sc.DatePurchased <= @date2  
    AND   sc.IsInCart = @shipped
) AS Result
SELECT 
    LastName, 
    DatePurchased, 
    SUM(Total)
FROM Result
GROUP BY 
    LastName,
    DatePurchased

So your problem is solved by removing the ud and sc identifiers from the outer select and group by.

yes, I was using a MS SQL Server / T-SQL, thank you for this re-written query. When I look at it like that it makes so much more sense. — Diana, May 07 '15 at 13:50

joining to tables in a query for ASP.NET C# ~ multi-part-identifier-could-not-be-bound

1 Answers1