none
How can I remove one left join

    Question

  • I have an access database and I need to change the working query, but every thing I tried gives me an error.  The Error I receive is "The join syntax is incorrect." I am trying to remove the portion  of the query related to generic D NextHigherAssembly.

    cmd.CommandText =
                        <SQL>
                       Select A.NIIN, A.FSC, A.Item_Name, A.DEMIL, B.AMC, B.AMSC, C.Company, D.NHA_NIIN, D.NHA_Item_Name, D.NHA_FSC, E.Date, E.Price, E.Qty, E.CAGE, E.Vendor_Name 
                                        FROM (((SegmentA As A INNER JOIN (Select DISTINCT MOERuleSegB.NIIN, MOERuleSegB.AMC, MOERuleSegB.AMSC FROM MOERuleSegB 
                                        WHERE MOERuleSegB.AMC  AND MOERuleSegB.AMSC )  AS B ON A.NIIN = B.NIIN) 
                                        INNER JOIN (SELECT MCRLMasterCrossReferenceList.NIIN, MCRLMasterCrossReferenceList.Company 
                                        FROM MCRLMasterCrossReferenceList WHERE MCRLMasterCrossReferenceList.Obsolescence='Design/Source Control Reference Item.')  AS C ON A.NIIN = C.NIIN) 
                                        LEFT JOIN (SELECT DISTINCT NextHigherAssembly.NIIN, NextHigherAssembly.NHA_NIIN, NextHigherAssembly.NHA_Item_Name, NextHigherAssembly.NHA_FSC FROM NextHigherAssembly)  AS D ON A.NIIN = D.NIIN) 
                                        LEFT JOIN (SELECT DISTINCT ProcurementHistory.NIIN, ProcurementHistory.Date, ProcurementHistory.Price, ProcurementHistory.Qty, ProcurementHistory.CAGE, ProcurementHistory.Vendor_Name FROM ProcurementHistory
                                        WHERE (((CDate(([ProcurementHistory].[Date])))>#12/31/2013#)))  AS E ON A.NIIN = E.NIIN
                         </SQL>
                    cn.Open()



    • Edited by VBShaper Tuesday, March 28, 2017 1:49 AM
    Monday, March 27, 2017 11:33 PM

Answers

  • When you remove the LEFT JOIN line, try keeping ‘)’ and check the correctness of parentheses.

    • Marked as answer by VBShaper Tuesday, March 28, 2017 11:07 AM
    Tuesday, March 28, 2017 10:28 AM

All replies

  • Have you tried to delete the whole LEFT JOIN line, then remove all of D.xxx fields?

    Tuesday, March 28, 2017 6:00 AM
  • Yes, I did remove both, but I receive the following exception.

    System.Data.OleDb.OleDbException was unhandled
      ErrorCode=-2147217900
      HResult=-2147217900
      Message=Syntax error in JOIN operation.
      Source=Microsoft Access Database Engine
      
       

    Tuesday, March 28, 2017 9:48 AM
  • When you remove the LEFT JOIN line, try keeping ‘)’ and check the correctness of parentheses.

    • Marked as answer by VBShaper Tuesday, March 28, 2017 11:07 AM
    Tuesday, March 28, 2017 10:28 AM
  • When you remove the LEFT JOIN line, try keeping ‘)’ and check the correctness of parentheses.

    That worked! Thank you.
    • Edited by VBShaper Tuesday, March 28, 2017 11:07 AM
    Tuesday, March 28, 2017 11:05 AM