Issue With JOIN Statement RRS feed

  • Question

  • I have a statement that is using OleDB in C# to select some data from a DBF table. I had an issue where when I selected something where it was equal to 0, it would select every field for that table.

    I managed to fix the statement in MySQL using a LEFT OUTER JOIN, but I am unsure on what the syntax is in FoxPro. Here is the statement so far;

    "SELECT s.scm_num, s.scm_name, c.cr3_text, q.qsp_value, s.scm_bprefix, s.scm_nxbnum FROM qspreset q INNER JOIN sdccomp s LEFT OUTER JOIN cntref3 c ON s.scm_cntref3=c.cr3_id AND q.qsp_relat=s.scm_invtype AND q.qsp_what=13";

    However I am getting a (not very useful) exception that simply says "Syntax error". I am attempting to do the LEFT OUTER JOIN on sdccomp and cntref3, whilst also doing a comparison using a value from qspreset.

    Can anybody explain where the syntax error is?

    • Edited by DRD94 Thursday, November 19, 2015 1:04 PM
    • Moved by CoolDadTx Thursday, November 19, 2015 3:59 PM ADO related
    Thursday, November 19, 2015 1:03 PM


  • Questions related to FoxPro need to be posted in their forums.  As for your query itself you should create the query in FoxPro using its tools and then you'll have the correct syntax to use for LINQ.  My gut instinct is your INNER JOIN.  You specify the INNER JOIN to sdccomp but you don't specify the ON clause to identify the column(s) to join against.  Each JOIN statement needs a corresponding ON clause, at least in every other DB I've worked with.

    Michael Taylor

    Thursday, November 19, 2015 3:58 PM