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

    Looking for a little ExecuteSQL help


      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

          Rob ---


          This might work:


          ExecuteSQL (






                    LEFT OUTER JOIN Phone ON

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


                    Person.ID = ?"

          ; "" ; <<desired personID>> )


          -- Tim

          • 2. Re: Looking for a little ExecuteSQL help

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

            • 3. Re: Looking for a little ExecuteSQL help

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