Error: Join expression not supported

This query works fine with "INNER JOIN" , however as soon as I change it to "LEFT JOIN", I get an error stating: "Join expression not supported". What am I doing wrong ? Thanks for help !!!


SELECT o.OrderNumber, o.SourceOrderID, od.SKU, skucost.SKU, od.CostPerUnit, skucost.Cost FROM ([Order Details Copy] AS od
LEFT JOIN [Orders Copy] AS o ON od.OrderNumber = o.OrderNumber)
LEFT JOIN [SKU Cost] AS skucost ON (o.SourceOrderID = skucost.OrderNumber) AND (od.SKU=skucost.SKU)

Ideally I need the query like this:

SELECT o.OrderNumber, o.SourceOrderID, od.SKU, skucost.SKU, od.CostPerUnit, skucost.Cost FROM ([Order Details Copy] AS od
INNER JOIN [Orders Copy] AS o ON od.OrderNumber = o.OrderNumber)
LEFT JOIN [SKU Cost] AS skucost ON (o.SourceOrderID = skucost.OrderNumber) AND (od.SKU=skucost.SKU)

If I remove "AND (od.SKU=skucost.SKU)", the query passes the validation and executes, however the result is not as expected without this statement.


tim2403Asked:
Who is Participating?
 
Pratima PharandeCommented:
SELECT o.OrderNumber, o.SourceOrderID, od.SKU, skucost.SKU, od.CostPerUnit, skucost.Cost FROM ([Order Details Copy] AS od
INNER JOIN [Orders Copy] AS o ON od.OrderNumber = o.OrderNumber)
LEFT JOIN [SKU Cost] AS skucost ON (o.SourceOrderID = skucost.OrderNumber)
where od.SKU=skucost.SKU
0
 
Pratima PharandeCommented:
SELECT o.OrderNumber, o.SourceOrderID, od.SKU, skucost.SKU, od.CostPerUnit, skucost.Cost FROM (([Order Details Copy] AS od
LEFT JOIN [Orders Copy] AS o ON od.OrderNumber = o.OrderNumber)
LEFT JOIN [SKU Cost] AS skucost ON (o.SourceOrderID = skucost.OrderNumber) AND (od.SKU=skucost.SKU))
0
 
tim2403Author Commented:
The issue appears to be with "od.SKU". It seems like I can't use od (Order Details Copy) table in that query for whatever reason. If I replace it with o.StringField = skucost.SKU - it works fine. Doesn't solve my issue though...
0
 
tim2403Author Commented:
Thanks for your quick reply pratima_mcs. However it doesn't solve an issue. Same error
0
 
tim2403Author Commented:
Thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.