FROM中的MS Access查询语法错误

For an assignement I have to write SQL out to display Customer's Last name, order date, product ID, fabric of the product, quantity ordered, and unit price. This must be a 4-table join and must user INNERJOIN even if where can be used.

对于分配,我必须编写SQL以显示客户的姓氏,订单日期,产品ID,产品结构,订购数量和单价。这必须是一个4表连接,即使可以在哪里使用,也必须使用INNERJOIN。

These are the tables involved.

这些是涉及的表格。

Cus (CID, Last, First, Phone)
Orders (OrdID, OrdDate, ShipDate, CID)
Manu (ManuID, Name, Phone, City)
Prods (PrID, ManuID, Category)
ProdFabric (PrID, Fabric, Price)
Orderline (OrdId, PrID, Fabric, Qty)

Cus(CID,最后,第一,电话)订单(OrdID,OrdDate,ShipDate,CID)Manu(ManuID,名称,电话,城市)产品(PrID,ManuID,类别)ProdFabric(PrID,Fabric,Price)订单(OrdId, PrID,Fabric,数量)

Bold means it is a primary key or part of a composite key.

粗体表示它是主键或复合键的一部分。

Here is the query I entered, but I keep getting a syntax error on FROM and it keeps highlighting Orders (after Cus INNERJOIN) for some reason.

这是我输入的查询,但我一直在FROM上遇到语法错误,并且由于某种原因它继续突出显示订单(在Cus INNERJOIN之后)。

SELECT Last, OrdDate, Prods.PrID, Fabric, Qty, Price  
FROM Cus INNERJOIN Orders INNERJOIN Orderline INNERJOIN ProdFabric   
ON OrderLine.PrID=ProdFabric.PrID  
AND ON Orderline.Fabric=ProdFrabric.Fabric  
AND ON Cus.CID=Orders.CID  
AND ON Orders.OrdID=Orderline.OrdID;

What causes this and how do I solve it?

是什么导致这种情况以及如何解决?

2 个解决方案

#1

AFAIK MS Access requires a Lisp-like proliferation of parentheses to support multiple joins.

AFAIK MS Access需要类似Lisp的括号扩散来支持多个连接。

Edit your MS Access queries in another editor that supports paren-matching and syntax-highlighting, e.g. Notepad++.

在另一个支持paren-matching和syntax-highlighting的编辑器中编辑MS Access查询,例如:记事本+ +。

Use table aliases to keep your queries shorter and more manageable.

使用表别名可以使查询更短,更易于管理。

With the parentheses, your query will look something like this:

使用括号,您的查询将如下所示:

SELECT c.Last
       , o.OrdDate
       , pf.PrID
       , pf.Fabric
       , ol.Qty
       , pf.Price
FROM   ((Cus c
       INNER JOIN Orders o ON c.CID = o.CID)
       INNER JOIN Orderline ol ON o.OrdID = ol.OrdID)
       INNER JOIN ProdFabric pf ON ol.PrID = pf.PrID;

#2

I don't think "AND ON" exists like you use it in your query.
You have to specify each join on its own.

我不认为“AND ON”就像你在查询中使用它一样存在。您必须自己指定每个连接。

SELECT ...
FROM cus INNER JOIN orders ON cus.cid = orders.cid
         INNER JOIN orderline ON orders.orderid = orderline.orderid
...

or you can also specify the join in your WHERE clause:

或者您也可以在WHERE子句中指定连接:

SELECT ...
FROM cus INNER JOIN orders 
         INNER JOIN orderline
...
WHERE cus.cid = orders.cid
  AND orders.orderid = orderline.orderid
...

Also: INNER JOIN are 2 words ;-)

另外:INNER JOIN是2个字;-)

#1