SQL：从多个表中选择多个列

I am using MS Access 2013.

我正在使用MS Access 2013。

I am trying to selecting the number and name from Salesperson table. Number, name and postcode from Customers table as well as all the information from the CarSale table all within the past month and order by salesperson no.

我正在尝试从Salesperson表中选择数字和名称。来自Customers表的编号，名称和邮政编码以及CarSale表中过去一个月内的所有信息以及销售员编号的订单。

I have come up with the following

我想出了以下内容

SELECT CS.carNo, CS.dateOfSale, SA.salespersonNo, SA.name AS SalesName, 
       CU.customerNo, CU.name AS CustName, CU.postCode  
FROM CarSale AS CS, Car AS C, Salesperson AS SA, Customer AS CU 
WHERE CS.carNo = C.carNo AND CS.salespersonNo = SA.salespersonNo 
AND CS.customerNo = CU.customerNo AND dateOfSale BETWEEN #01/09/2016#  
AND #02/09/2016# 
ORDER BY CS.salespersonNo;

However as you can see, this is butt-ugly! I did some research and found that I should be using "JOINS" so I went ahead and included them, this is where my problem starts.

但是你可以看到，这很丑陋！我做了一些研究，发现我应该使用“JOINS”，所以我继续包括它们，这是我的问题开始的地方。

After inserting the JOINS into the query I get something that looks like this:

将JOINS插入查询后，我得到的内容如下所示：

SELECT CS.carNo, CS.dateOfSale, SA.salespersonNo, SA.name AS SalesName,
       CU.customerNo, CU.name AS CustName, CU.postCode 
FROM CarSale AS CS 
JOIN Car AS C ON CS.carNo = C.carNo 
JOIN Salesperson AS SA on CS.salespersonNo = SA.salespersonNo 
JOIN Customer AS CU ON CS.customerNo = CU.customerNo 
WHERE cs.dateOfSale BETWEEN #01/09/2016# AND #02/09/2016# 
ORDER BY CS.salespersonNo;

Here are the tables:

以下是表格：

**CarSale**
carNo    salespersonNo    customerNo    dateOfSale
-------------------------------------------------------


**Salesperson**
salespersonNo    name    contactNo    monthlySalary  centreNo
--------------------------------------------------------------


**Customer** 
customerNo    name    contactNo   postCode
---------------------------------------------

The error I am getting is "Syntax error in FROM clause."

我得到的错误是“FROM子句中的语法错误。”

2 个解决方案

#1

I think you're close, but there is something wonky about your JOINs - you have a join on 'Car', but that's not one of your tables. JOINing occurs between tables, with ON specifying the fields that are equivalent (what you are JOINing ON). With that in mind:

我觉得你很亲密，但是你的JOIN有一些不可思议的东西 - 你加入'Car'，但那不是你的桌子之一。表之间发生JOIN，ON指定等效的字段（您正在JOIN ON ON）。考虑到这一点：

SELECT s.salespersonNo, s.name, c.customerNo, cs.carNo, 
       cs.dateofsale, c.name, c.postCode
FROM salesperson s
INNER JOIN carsale cs
ON cs.salespersonNo = s.salespersonNo
INNER JOIN customer c
ON cs.customerNo = c.customerNo
WHERE cs.dateOfSale BETWEEN #01/09/2016# AND #02/09/2016# 
ORDER BY CS.salespersonNo;

Notice that your WHERE and ORDER BY are unchanged, and I just used different aliases in my test run. The main difference is in the JOIN - I join from salesperson to CarSales ON the salespersonNo, and then from CarSales to customerNo, similar to what you already have.

请注意，您的WHERE和ORDER BY未更改，我在测试运行中只使用了不同的别名。主要的区别在于JOIN - 我从销售人员到销售人员的CarSales，然后从CarSales加入到customerNo，类似于您已有的。

#2

The syntax error is because with multiple JOINs you need parentheses around every pair of them.

语法错误是因为对于多个JOIN，您需要在每对JOIN周围使用括号。

It would be a lot easier to use the query designer, it does those things automatically.

使用查询设计器要容易得多，它会自动执行这些操作。

SELECT CS.carNo, CS.dateOfSale, SA.salespersonNo, SA.name AS SalesName,
       CU.customerNo, CU.name AS CustName, CU.postCode 
FROM (((CarSale AS CS 
  INNER JOIN Car AS C ON CS.carNo = C.carNo) 
  INNER JOIN Salesperson AS SA on CS.salespersonNo = SA.salespersonNo) 
  INNER JOIN Customer AS CU ON CS.customerNo = CU.customerNo) 
WHERE cs.dateOfSale BETWEEN #01/09/2016# AND #02/09/2016# 
ORDER BY CS.salespersonNo;

As Stidgeon wrote, if these are all fields you need, you can omit the join with Car.

正如Stidgeon所写，如果这些都是您需要的所有字段，您可以省略与Car的连接。