CODEWITHSUNDEEP
sqlsql-servert-sql
Help123
Help123

Reputation: 1443

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

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?

Upvotes: 0

Views: 1177

Answers (2)

Eric
Eric

Reputation: 703

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';

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

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';

Upvotes: 1

Related Questions