TSQL multi-part identifier could not be bound (explicit join for select x table in inner join)

0
votes

I am running into a problem because I am trying to do an explicit join in an inner join where I am trying to select certain values from another table.

My SQL:

SELECT A.OrderID
     , A.ItemID
     , A.Line
     , B.LID
     , B.Quantity 
FROM Sales A 
INNER JOIN (SELECT X.OrderID
                 , X.Line
                 , SUM(X.Quantity) AS Quantity
                 , X.DestinationID
                 , X.SerialNum 
            FROM SalesDetail X 
            WHERE X.OrderID = A.OrderID 
            GROUP BY X.OrderID, X.Line, X.DestinationID, X.SerialNum
           ) AS C
            ON A.Line = C.Line 
           AND B.Quantity = C.Quantity 
           AND B.SerialNum = C.SerialNum
INNER JOIN SalesContents B 
            ON A.OrderID = B.OrderID 
           AND A.ItemID = B.ItemID
WHERE A.OrderID = '12345'
AND  A.ItemID = 'ABC123';

Now I can remove the WHERE clause within the X table and filter it within the inner join but I would like to filter it right when I am querying the values. Is there a way to rewrite this statement so that the unbound identifier can be worked around?

2
sqlsql-servertsql
just move the conditions AND B.Quantity = C.Quantity AND B.SerialNum = C.SerialNum after you join SalesContents B (alias). – Vamsi Prabhala
Your query just makes no sense. The join conditions refer to table aliases before they are defined. I find it helpful to use table abbreviations for the table aliases (such as s for sales rather than a). If you fix the query to order the conditions correctly and remove the where clause in the subquery, your might be able to fix the query yourself. – Gordon Linoff

2 Answers

1
votes

You cannot refer an alias in your joins which hasn't been defined.

If you take the subquery in the end only then you can join it to Alias C, Something like ....

SELECT A.OrderID
     , A.ItemID
     , A.Line
     , B.LID
     , B.Quantity 
FROM Sales A 
INNER JOIN SalesContents B 
            ON A.OrderID = B.OrderID 
           AND A.ItemID = B.ItemID
INNER JOIN (SELECT X.OrderID
                 , X.Line
                 , SUM(X.Quantity) AS Quantity
                 , X.DestinationID
                 , X.SerialNum 
            FROM SalesDetail X 
            WHERE X.OrderID = A.OrderID 
            GROUP BY X.OrderID, X.Line, X.DestinationID, X.SerialNum
           ) AS C
            ON A.Line = C.Line 
           AND B.Quantity = C.Quantity 
           AND B.SerialNum = C.SerialNum
WHERE A.OrderID = '12345'
AND  A.ItemID = 'ABC123';
0
votes

Move your subquery's WHERE clause to the join's ON clause. Alias 'A' is not defined in your subquery.

SELECT A.OrderID
     , A.ItemID
     , A.Line
     , B.LID
     , B.Quantity 
FROM Sales A 
INNER JOIN (SELECT X.OrderID
                 , X.Line
                 , SUM(X.Quantity) AS Quantity
                 , X.DestinationID
                 , X.SerialNum 
            FROM SalesDetail X 
            --WHERE X.OrderID = A.OrderID 
            GROUP BY X.OrderID, X.Line, X.DestinationID, X.SerialNum
           ) AS C
            ON A.Line = C.Line 
           AND C.OrderID = A.OrderID --<-- move the filter here
           AND B.Quantity = C.Quantity 
           AND B.SerialNum = C.SerialNum
INNER JOIN SalesContents B 
            ON A.OrderID = B.OrderID 
           AND A.ItemID = B.ItemID
WHERE A.OrderID = '12345'
-- you could also put it here instead
AND  A.ItemID = 'ABC123';