I have a JOIN spanning 4 tables, which works - all are Inner JOINs. However, if
I use the wizard to make one a LEFT OUTER, then when I use the wizard to
complete it I receive an error "Generated SELECT statement. Join
expression not supported." I understand that the logic must contain some
ambiguity, but I don't know how to clean it up.
My primary table is the tblWorkOrder table and it should join tblPOLineItems
with a Left Outer join. There is one additional join on each side of this Left
Outer -- tblWorkOrder joining with tblProducts and tblPOLineItems joining with
tblPurchaseOrders. Those are both fine using Inner joins. I need to be able to
display those WorkOrders that have not been assigned to POLineItems.
Any help would be appreciated.
Here is the query as the wizard created it...
SELECT tblWorkOrder.WorkOrderNumber, tblWorkOrder.POLineItemID,
tblWorkOrder.WorkOrderStatus, tblWorkOrder.ActualStartDate,
tblWorkOrder.ActualCompletionDate,
tblWorkOrder.ReceivedDate, tblWorkOrder.AssignedTo, tblWorkOrder.DateAssigned,
tblWorkOrder.AcceptedBy,
tblWorkOrder.DateAccepted, tblWorkOrder.DelegatedTo,
tblWorkOrder.Quantity, tblWorkOrder.UnitPrice, tblWorkOrder.TotalPrice,
tblWorkOrder.Due,
tblWorkOrder.ProductID, tblWorkOrder.EnteredDate,
tblWorkOrder.EnteredBy, tblPOLineItems.PurchaseOrderLineNumber,
tblPOLineItems.PurchaseOrderID,
tblPurchaseOrders.PONumber, tblProducts.ProductDescription, tblWorkOrder.Refurb
FROM ((tblProducts INNER JOIN
tblWorkOrder ON tblProducts.ProductID =
tblWorkOrder.ProductID) LEFT OUTER JOIN
(tblPurchaseOrders INNER JOIN
tblPOLineItems ON tblPurchaseOrders.PurchaseOrderID =
tblPOLineItems.PurchaseOrderID) ON
tblWorkOrder.POLineItemID = tblPOLineItems.POLineItemID)
WHERE (tblWorkOrder.WorkOrderNumber = ?)
|
Not sure I got it correctly (it's hard to write SQL without being able to try it
out), but to me it seems you cannot do without replacing one of the INNER JOINs
with an OUTER JOIN, as you have two JOINs that rely on tblPOLineItems, which is
ultimately outer joined. So my suggestion is:
FROM tblWorkOrder
INNERJOIN tblProducts ON tblProducts.ProductID = tblWorkOrder.ProductID
LEFTOUTER JOIN tblPOLineItems ON tblWorkOrder.POLineItemID =
tblPOLineItems.POLineItemID
LEFTOUTER JOIN tblPurchaseOrders ON tblPurchaseOrders.PurchaseOrderID =
tblPOLineItems.PurchaseOrderID
|