语法错误加入3个表

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

我试图用以下sql语句加入3个表

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'."

“查询表达式中的语法错误(缺少运算符)'orderDetail_table.OrderID = order_table.orderid在item_table.itemID = orderDetail_table.itemID'上留下外连接item_table。”

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

我希望它总是返回orderDetail_table.price * orderDetail_table.quantity的总和,orderDEtail_table.ItemID可能没有item_table.GLAcct

2 个解决方案

#1

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

#2

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.

由于OrderDetail表始终是Order的一部分,并且每个细节始终引用一个Item,因此可以用INNER JOIN替换LEFT OUTER JOIN。特别是,由于WHERE和GROUP BY子句都引用了可选表。

What exactly is the error message?

究竟是什么错误信息?

#1