Having Syntax errors Joining 3 tables

Question

I am trying to join 3 tables with the following sql statement

Select 
    Sum(OrderDetail_table.Price * orderDetail_table.quantity) as totalPrice,
    item_table.GLacct 
from 
    OrderDetail_table 
    left outer join order_table on 
        orderDetail_table.orderID = order_table.orderid 
    left outer join item_table on 
        item_table.itemID = orderDetail_table.itemID 
where 
    Order_table.invoiceDate = #01/31/2009# 
group by 
    item_table.glacct

The error message I am getting is:

"Syntax error(missing operator) in query expression 'orderDetail_table.OrderID = order_table.orderid left outer join item_table on item_table.itemID = orderDetail_table.itemID'."

Any help would be appreciated. Thanks

I want it always return the Sum of the orderDetail_table.price * orderDetail_table.quantity, there might not be an item_table.GLAcct for an orderDEtail_table.ItemID

Answer 1

As the OrderDetail table is always part of an Order, and each detail always references an Item, you can replace the LEFT OUTER JOIN by INNER JOINs. Especially, since the WHERE and GROUP BY clauses both reference the optional tables.

What exactly is the error message?

Answer 2

Try brackets:

Select
    Sum(OrderDetail_table.Price * orderDetail_table.quantity) as totalPrice,
    item_table.GLacct 
from
     (OrderDetail_table
     left outer join order_table on
         orderDetail_table.orderID = order_table.orderid)
     left outer join item_table on
         item_table.itemID = orderDetail_table.itemID
where
     Order_table.invoiceDate = #01/31/2009#
group by
     item_table.glacct