c# - MS Access 数据库 SQL 查询

Question

我有 3 个表，分别称为发票、客户和公司。我想使用 查询 将这 3 个表合并为一个表。发票表中包含客户 ID 和公司 ID。如何连接 3 个表？

我尝试了发票和客户表与此查询配合良好。但我不知道用这个添加第三个表。

SELECT RPT_Invoice_Less.InvoiceNumber, RPT_Invoice_Less.Terms,
    RPT_Invoice_Less.Invoicedate, RPT_Invoice_Less.OurQuote,
    RPT_Invoice_Less.SalesPerson, RPT_Customer.CustomerName,
    RPT_Customer.CustomerId, RPT_Customer.ContactPerson,
    RPT_Customer.BillingAddress, RPT_Customer.DeliveryAddress,
    RPT_Invoice_Less.OrderNumber, RPT_Invoice_Less.ShippingBy,
    RPT_Invoice_Less.ShipReferenceNo, RPT_Invoice_Less.Notes,
    RPT_Invoice_Less.Price, RPT_Invoice_Less.Discount,
    RPT_Invoice_Less.Shipping, RPT_Invoice_Less.Tax,
    RPT_Invoice_Less.GrandTotal, RPT_Invoice_Less.Company
FROM RPT_Invoice_Less
INNER JOIN RPT_Customer
ON RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId;

此代码适用于 2 个表

SELECT RPT_Invoice_Less.InvoiceNumber, RPT_Invoice_Less.Terms, RPT_Invoice_Less.Invoicedate, RPT_Invoice_Less.OurQuote, RPT_Invoice_Less.SalesPerson, RPT_Customer.CustomerName, RPT_Customer.CustomerId, RPT_Customer.ContactPerson, RPT_Customer.BillingAddress, RPT_Customer.DeliveryAddress, RPT_Invoice_Less.OrderNumber, RPT_Invoice_Less.ShippingBy, RPT_Invoice_Less.ShipReferenceNo, RPT_Invoice_Less.Notes, RPT_Invoice_Less.Price, RPT_Invoice_Less.Discount, RPT_Invoice_Less.Shipping, RPT_Invoice_Less.Tax, RPT_Invoice_Less.GrandTotal, RPT_OrionSystem.Company, RPT_OrionSystem.CompanyId
FROM RPT_Invoice_Less 
INNER JOIN RPT_Customer 
ON RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId
INNER JOIN RPT_OrionSystem
ON RPT_Invoice_Less.CompanyId = RPT_OrionSystem.CompanyId;

此代码显示语法错误。

帮我添加第三个公司表。

Answer 1

假设您在 RPT_Customer 表或 RPT_Invoice_Less 中有一个 CompanyID 字段(或类似的字段)，它只是一个添加另一个 INNER JOIN 很重要

....
FROM ((RPT_Invoice_Less
INNER JOIN RPT_Customer
ON RPT_Invoice_Less.CustomerId = RPT_Customer.CustomerId)
INNER JOIN RPT_OrionSystem 
ON  RPT_Invoice_Less.CompanyID = RPT_OrionSystem.CompanyID)