CODEWITHSUNDEEP
sqlms-accessleft-joininner-join
JeffO
JeffO

Reputation: 137

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

Upvotes: 1

Views: 1367

Answers (3)

JeffO
JeffO

Reputation: 137

Turns out the problem was with the multiple conditions in the left join.

I added parens and it solved it.

    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'

Upvotes: 2

Tharsan Sivakumar
Tharsan Sivakumar

Reputation: 6531

    select y.IngrSKU, y.IngMeasurementID, y.productName, 
y.quantity as   mQuantity, y.quantity, y.lot  
    from  ((select *
        from Menu m 
        inner join sku s on m.ingrsku = s.sku ) x
        left outer join lot l on x.ingrsku + '-070516j' = l.lot 

and l.destinationid = 2 ) y
        where y.skutype = 4 and y.SKU = '1321'

Upvotes: -1

Peter P
Peter P

Reputation: 16

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'

Upvotes: 0

Related Questions