Please see my below SQL connecting MS Access 2007
i need to connect 3 table using with INNER JOIN
see below code
txtOrderNumber.Text = ReccursingOrderNumber;
string FindRecurssinEditSQL = "SELECT ST.Select_order_price AS Select_order_price,ST.Reference_Number AS RNumber,ST.Order_Date As Odate, ST.CustomerID AS STCustomerID, ST.Sales_Person_Name as STSales_Person_Name, ST.Delivery_To as STDelivery_To, ST.Sales_Type As STSales_Type, ST.Remarks As STRemark, ST.Total_Price As STTotal_Price, ST.Discount As STDiscount, ST.Tax As STTax, ST.NetAmount As STNetAmount, ST.ShipAddress As STShipAddress, ST.Shipby As STShipby, ST.ShippingCost AS STShippingCost, ST.PrivateNote AS STPrivateNote, ST.Terms AS STTerms, ST.NumberofDays AS STNumberofDays, ST.AmountDue AS STAmountDue, ST.Address AS STAddress, ST.Method_of_Payments AS STMethod_of_Payments,";
FindRecurssinEditSQL += "ProductProcess_Details.OrderNumber AS P_OrderNumber, ProductProcess_Details.SubProduct_Number AS P_SubProduct_Number, ProductProcess_Details.Quantity AS P_Quantity, ProductProcess_Details.Retail_Price AS P_Retail_Price,ProductProcess_Details.Old_Recurrsion_Retail_Price AS P_Old_Recurrsion_Retail_Price,";
FindRecurssinEditSQL += "SB.S_Product_Name AS SB_S_Product_Name,SB.S_Measure_Type As S_Measure_Type,";
FindRecurssinEditSQL += "Cus.Cus_Fname AS Cus_Fname";
FindRecurssinEditSQL += " ";//createing space for Sql From tag
FindRecurssinEditSQL += "From (StockControl As ST INNER JOIN SubProduct AS SB ON SB.S_Product_Code=ProductProcess_Details.SubProduct_Number) (INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerID) Where ST.Order_Number='" + ReccursingOrderNumber + "' AND ProductProcess_Details.OrderNumber='" + ReccursingOrderNumber + "'";
OleDbCommand Editcmd = con.CreateCommand();
Editcmd.CommandText = FindRecurssinEditSQL;
OleDbDataReader editReader = Editcmd.ExecuteReader();
Syntax error in JOIN operation.
its worked with 2 join but when i connect with 3 table its showing Error.
please show me where i'm doing mistake.thx
(INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerID)
I think your statement will work if you remove the ") (" immediately before the second inner join making it:
FindRecurssinEditSQL += "From (StockControl As ST INNER JOIN SubProduct AS SB ON SB.S_Product_Code=ProductProcess_Details.SubProduct_Number INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerID) Where ST.Order_Number='" + ReccursingOrderNumber + "' AND ProductProcess_Details.OrderNumber='" + ReccursingOrderNumber + "'";
Best guess though from just reading your code.
All advice is offered in good faith only. You are ultimately responsible for effects of your programs and the integrity of the machines they run on.
I try using your above statement but still i'm getting below Error, i just wondering how to put brackets to make it separatly
Syntax error (missing operator) in query expression 'SB.S_Product_Code=ProductProcess_Details.SubProduct_Number INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerI'.