我需要在列表框中显示已购买产品（基于用户搜索）的客户。我在Access中有五个不同的表，它们存储不同的信息，并且彼此之间具有ID（在vb中使用combox框）相关联。我需要能够搜索产品，例如“白面包”，然后程序应显示存储在数据库中的客户的全名和地址。
Table: TransactionDetails
Fields: ID, stockID, custTransID
Table: CustomerTransaction
Fields: ID, custID, dateOfTransaction
Table: CustomerAccountDetails
Fields: ID, custFullName, custAddress, custLandline,
custMobile, custDOB, custCreditDetails
Table: StockDescription
Fields: ID, stockName, stockDesc, stockPrice
Table: SupplierDetails
Fields: ID, supplierName, supplier Address
我想我需要使用INNER JOIN一次查询多个表，但是我不确定语法（我是SQL的新手）。到目前为止，我有这个：
Dim productSearch As String
productSearch = productSrchInput.Text
Dim databaseConnection As New OleDb.OleDbConnection
Dim counter As Integer
Dim array(10) As String
databaseConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=assignment5database.accdb"
databaseConnection.Open()
Dim searchDatabase As OleDbCommand = New OleDbCommand("SELECT CustomerAccountDetails.custFullName, CustomerAccountDetails.custAddress " & _
"FROM CustomerAccountDetails " & _
"INNER JOIN StockDescription ON TransactionDetails.stockID = TransactionDetails.custTransID " & _
"WHERE StockDescription.stockName = '" & productSearch & "'", databaseConnection)
Dim searchResults As OleDbDataReader = searchDatabase.ExecuteReader
counter = 1
Do While searchResults.Read
srchResultsList.Items.Add(searchResults.Item(0))
counter += 1
Loop
databaseConnection.Close()
您缺少将客户连接到股票详细信息的某些联接。这是Access希望根据您的描述提取数据的SQL。如果您习惯使用SQL Server或MySQL，则括号看起来似乎是多余的，但是如果您不使用括号，则Access会适合您的情况。
SELECT CustomerAccountDetails.custFullName, CustomerAccountDetails.custAddress, StockDescription.stockName
FROM StockDescription
INNER JOIN ((CustomerAccountDetails
INNER JOIN CustomerTransaction ON CustomerAccountDetails.ID = CustomerTransaction.custID)
INNER JOIN TransactionDetails ON CustomerTransaction.ID = TransactionDetails.custTransID) ON StockDescription.ID = TransactionDetails.StockID
WHERE StockDescription.stockName="something"
正如Fionnuala指出的那样，在将其放入代码之前，我几乎总是会使用Access查询设计器构建一个包含多个联接的查询。我几乎总是省略一组括号，或尝试以SQL Server期望并被Access拒绝的结构编写查询。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句