C# SQL connect with 3 tables

[Ryan1977]

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 + "'";
                    //MessageBox.Show(FindRecurssinEditSQL.ToString());
                    //creating Olecommand
                    OleDbCommand Editcmd = con.CreateCommand();
                   
                    Editcmd.CommandText = FindRecurssinEditSQL; 
                   
                    OleDbDataReader editReader = Editcmd.ExecuteReader();

ERROr is
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

[BioPhysEngr]

This is not an independent clause:

(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.

Victory?

[Ryan1977]

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'.

[Arjay]

Create the join syntax in the Access designer and copy the generated sql into your c# code. Fix up as appropriate.