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

    SQL Join Difficulties


      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 ?";"|";"";





      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??



        • 1. Re: SQL Join Difficulties

          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 ?";"|";"";





          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

            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

              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 
                   ,\"Business Type\"
              FROM Machine INNER JOIN 
                   Customer ON \"_AccNo\" = AccNo
              WHERE Model LIKE ? 
                   AND ActiveDead LIKE ? 
                   AND \"Business Type\" LIKE ? "


              Check this out, Cathy:




              1 of 1 people found this helpful