我正在用PHP编写一个针对Access数据库的SQL查询,我得到了以下错误:
"SQLSTATE[42000]:语法错误或访问冲突:-3100[Microsoft][ODBC Microsoft access Driver]查询表达式'(FROM[Tbl_Invoice_Details],INNER JOIN[Tbl_Product_Table]ON[Tbl_IInvoice_Detail.Product_ID]=[Tbl_Poduct_Table.Product_ID])WHERE Invoice_ID=Pa_RaM000'中的语法错误(缺少运算符)。"
这是我的问题:
SELECT [Tbl_Invoice_Details.Product_ID],
[Tbl_Invoice_Details.Seed Size], [Tbl_Invoice_Details.Ref2], [Tbl_Invoice_Details.RefNo], [Tbl_Invoice_Details.Quantity Ordered],
[Tbl_Invoice_Details.Quantity Delivered], [Tbl_Invoice_Details.PricePerUnit], [Tbl_Invoice_Details.Extended],
[Tbl_Product_Table.Product_ID], [Tbl_Product_Table.Hybrid Brand], (FROM [Tbl_Invoice_Details] INNER JOIN [Tbl_Product_Table]
ON [Tbl_Invoice_Details.Product_ID] = [Tbl_Product_Table.Product_ID]) WHERE Invoice_ID = ?
为什么会失败?
您的查询失败,因为方括号中的所有内容都被解释为单个名称,因此
SELECT [Tbl_Invoice_Details.Product_ID] ... FROM [Tbl_Invoice_Details]
与相同
SELECT [Tbl_Invoice_Details].[Tbl_Invoice_Details.Product_ID] ... FROM [Tbl_Invoice_Details]
Access不允许我们定义包含句点的字段名,因此这永远不会起作用。
你的意思是
SELECT [Tbl_Invoice_Details].[Product_ID] ... FROM [Tbl_Invoice_Details]
这是表达的正确方式之一
SELECT [TableName].[FieldName] ... FROM [TableName]
下面的查询如何:
SELECT i.Product_ID, i.[Seed Size], i.Ref2, i.RefNo, i.[Quantity Ordered],
i.[Quantity Delivered], i.PricePerUnit, i.Extended, p.[Hybrid Brand]
FROM Tbl_Invoice_Details i
INNER JOIN Tbl_Product_Table p ON i.Product_ID = p.Product_ID
WHERE i.Invoice_ID = ?
实际上,[Tbl_Product_Table.Hybrid_Brand]后面的逗号是不需要的。。也许你的发票id在锚之间
所以WHERE Invoice_ID = Pa_RaM000
变成WHERE Invoice_ID = "Pa_RaM000"