suffa suffa - 3 months ago 8
SQL Question

MS SQL 2014 sql mulitpart identifier cannot be bound

I'm still a neophyte when it comes to sql. So, can somewhat direct me in why I'm getting the following error:

Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "ClaimSetOrder.OrderLineItemID" could not be bound.


ClaimSetOrder.OrderLineItemID = FK, OrderLineItem.OrderLineItemID = PK

Below is the script:

use mydatabase

SELECT OrderLineItem.OrderID
,OrderLineItem.OrderLineItemID
,OrderLineItem.DrugID
,ClaimSet.NDCNumber
,OrderLineItem.OverageAmountUsed
,OrderLineItem.BottleQuantity
,OrderLineItem.BottleQuantityBatched
,ClaimSetOrder.ClaimSetOrderID
,ClaimSet.ClaimSetID
,ClaimSet.PillsRequired

FROM ClaimSet
INNER JOIN OrderLineItem ON OrderLineItem.OrderLineItemID = ClaimSetOrder.OrderLineItemID
INNER Join ClaimSetOrder ON ClaimSetOrder.ClaimSetID = ClaimSet.ClaimSetID

WHERE OrderLineItem.OrderID = XXXX

Answer

You need to specify the table before using it in the on clause. So, rearrange the order of the conditions in the FROM:

FROM ClaimSet INNER JOIN
     ClaimSetOrder
     ON ClaimSetOrder.ClaimSetID = ClaimSet.ClaimSetID INNER JOIN
     OrderLineItem
     ON OrderLineItem.OrderLineItemID = ClaimSetOrder.OrderLineItemID 

Note: I would also recommend that you use table aliases to simplify the query construction:

FROM ClaimSet cs INNER JOIN
     ClaimSetOrder cso
     ON cso.ClaimSetID = cs.ClaimSetID INNER JOIN
     OrderLineItem oli
     ON oli.OrderLineItemID = cso.OrderLineItemID 

This requires changing all the column references throughout the query.

Comments