JeffO JeffO - 4 months ago 20
SQL Question

Nested Inner Join With Outer Join in MS ACCESS

I've reviewed some of the previous questions regarding this topic and can't seem to get an answer to my issue.

I have 3 tables (Lot, Menu, SKU).

I need all the ingredients in Menu and their associated Product names in SKU for a given SKU, even if there is no associated row in LOT

My current query:

select m.IngrSKU, m.IngMeasurementID, s.productName, m.quantity as mQuantity, l.quantity, l.lot
from (Menu m
inner join sku s on m.ingrsku = s.sku)
left outer join lot l on m.ingrsku + '-070516j' = l.lot and l.destinationid = 2
where m.skutype = 4 and m.SKU = '1321'


I read that the outer join has to come after the inner, but I'm still getting the "Join Expression Not Supported" error.

Any ideas?

Update: this query provides the desired resultset in SQL Server; just can't get it to run in Access

Answer

Did you try without the brackets?

select m.IngrSKU, m.IngMeasurementID, s.productName, m.quantity as , mQuantity, l.quantity, l.lot 
from Menu m 
join sku s on m.ingrsku = s.sku
left join lot l on m.ingrsku + '-070516j' = l.lot and l.destinationid = 2 
where m.skutype = 4 and m.SKU = '1321'