3 Replies Latest reply on Nov 19, 2012 6:00 AM by beverly

    SQL Join Difficulties

    Gingernut

      I am running Filemaker12 on WIndows & and exploring SQL SQLBuilder Most of the time things work as expected but I cannot seem to get this to work

       

      Tables are Customer and Machine

      Joined on Acc No or _Acc No in both tables

      I want to show all the Machines beginning with M that are Active (begin with A) and I want to show the Business Type from the Customer Table

      As soon as I add the Business Type to the WHERE the SQL breaks

      The Join is working as before I add the above line I can see the Business Type

       

       

       

      // Name: Machines Installed

      // Date: 11/19/2012

      ExecuteSQL ("SELECT Machine.ActiveDead , Machine.Model , Customer."Business Type"

      FROM Machine Machine

      INNER JOIN Customer Customer ON Machine."_AccNo"=Customer.AccNo

      WHERE Model LIKE ? AND ActiveDead LIKE ? AND "Business Type" LIKE ?";"|";"";

      "M%";

      "A%";

      "Edu%")

       

      Any ideas much appreciated as I am loving this SQL stuff when it works

       

      Also why is it showing from Machine Machine ie naming the table twice??

       

      CAthy

        • 1. Re: SQL Join Difficulties
          wimdecorte

          Probably because you don't specify what table your WHERE requests come from.

           

          Try something like this:

           

          ExecuteSQL ("SELECT M.ActiveDead , M.Model , C.\"Business Type\"

          FROM Machine M

          INNER JOIN Customer C ON M.\"_AccNo\"=C.AccNo

          WHERE M.Model LIKE ? AND M.ActiveDead LIKE ? AND C.\"Business Type\" LIKE ?";"|";"";

          "M%";

          "A%";

          "Edu%")

           

          I've replaced "machine machine" with "machine M" to make the concept of table naming in the query a little clearer

          • 2. Re: SQL Join Difficulties
            Gingernut

            Hi Wim

            Thanks so much I guess I need to learn more for myself and rely less on "tools"

            It is now working with the code manually edited

            • 3. Re: SQL Join Difficulties
              beverly

              the "alias" is not required if you don't have fields/columns named the same in different tables. It's strictly for convenience otherwise.

              ExecuteSQL ( " SELECT 
                   ActiveDead 
                   ,Model 
                   ,\"Business Type\"
              FROM Machine INNER JOIN 
                   Customer ON \"_AccNo\" = AccNo
              
              WHERE Model LIKE ? 
                   AND ActiveDead LIKE ? 
                   AND \"Business Type\" LIKE ? "
              ;"|";"";
              "M%";
              "A%";
              "Edu%")
              

               

              Check this out, Cathy:

              http://www.filemakerhacks.com/?p=6605

               

              Beverly

              1 of 1 people found this helpful