3 Replies Latest reply on Dec 5, 2013 12:23 PM by DuckSportsFan

    Looking for a little ExecuteSQL help

    RobWestergaard

      Here's a basic version of my setup: I have a "Person" table and a "Phone" table. The Person table has a unique primary key, and a person's name. The Phone table has a PersonID foreign key, a type attribute, and a phone number. A Person can have zero, one, or many child Phone records.

       

      I want to create an ExecuteSQL statement that will, given a PersonID, return the person's name and, if they have a phone number with the type "Mobile", return that. I still want the statement to return the person's name even if there's no Phone record of type "Mobile".

       

      I know how to get my desired result from records that have an associated Mobile number:

       

      ExecuteSQL (

      "SELECT a.person_name, b.phone FROM Person a

      INNER JOIN Phone b ON a.ID = b.PersonID

      WHERE b.Type = 'Mobile' AND a.ID = ?" ;

      "" ; "" ; <<desired personID>> )

       

      …but this doesn't return Person records without an associated Mobile number. I know how to get a person's name and zero or more associated phone numbers:

       

      ExecuteSQL (

      "SELECT a.person_name, b.phone FROM Person a

      LEFT OUTER JOIN PHONE b ON a.ID = b.PersonID

      WHERE a.ID = ?" ;

      "" ; "" ; <<desired personID>> )

       

      …but this doesn't filter out the non-Mobile numbers, and returns more than one row per Person if there are multiple Phone records for that person.

       

      Is there a way to do this with FileMaker ExecuteSQL? I'm not experienced enough with either CASE statements or nested SELECT statements to decide if it's possible that way.

        • 1. Re: Looking for a little ExecuteSQL help
          TimDietrich

          Rob ---

           

          This might work:

           

          ExecuteSQL (

          "SELECT

                    Person_Name,

                    Phone

          FROM

                    Person

                    LEFT OUTER JOIN Phone ON

                              ( ( Phone.PersonID = Person.ID ) AND ( Phone.Type = 'Mobile' ) )

          WHERE

                    Person.ID = ?"

          ; "" ; <<desired personID>> )

           

          -- Tim

          • 2. Re: Looking for a little ExecuteSQL help
            RobWestergaard

            Thank you, Tim. That did indeed do the trick!

            • 3. Re: Looking for a little ExecuteSQL help
              DuckSportsFan

              I was looking for this exact thing...  But using SeedCode's approach worked on FMS13 and Tim's didn't...But maybe I had typos. So here's another method.

              Need to extract multiple child records for a specific parent record. FOR CALCULATED FIELD USE, DO NOT STORE RESULTS!

              People (a) = parent TO

              Phones (b) = child TO

              Here's the code:

               

              // ------------  BEGIN EXECUTESQL BLOCK ------------ 

               

               

              Let ( [

               

               

              var = id; // current person record id

              ReturnSub = "\n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  \n is the default.

              SQLResult = ExecuteSQL (

               

               

              // ------------  BEGIN QUERY ------------

              // use of escaped quotes ensures compatibility

               

               

              "SELECT b.\"Label\", b.\"Value\"

              FROM \"People » Phones\" b

              INNER JOIN \"People\" a ON b.\"id_People\" = a.\"id\"

              WHERE ((b.\"Type\" LIKE ? OR b.\"Type\" LIKE ?) AND (b.\"id_People\" = ?))

              ORDER BY b.\"Priority\" ASC" ;

               

               

              // ------------  END QUERY ------ ------

               

               

              // ------------  BEGIN FIELD AND ROW SEPARATORS ------------

               

               

              "    " ; "|*|" ;

               

               

              // ------------  END FIELD AND ROW SEPARATORS ------------

               

               

              // ------------  BEGIN ARGUMENTS ------------ 

              // ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

               

               

              "Phone" ; "Fax" ; var

               

               

              // ------------  END ARGUMENTS ------------ 

               

               

              ) ] ;

               

               

              // ------------  BEGIN CARIAGE RETURN SUBSTITUTIONS ------------ 

               

               

              Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

               

               

              // ------------  END CARIAGE RETURN SUBSTITUTIONS ------------ 

               

               

              )

               

               

              // Compliments of SeedCode… Cheers!

               

               

              // ------------  END EXECUTESQL BLOCK ------------