Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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.


0
tim2403
Asked:
tim2403
  • 3
  • 2
1 Solution
 
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
 
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
 
tim2403Author Commented:
Thanks !
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now