AnsweredAssumed Answered

Looking for a little ExecuteSQL help

Question asked by RobWestergaard on Jun 7, 2013
Latest reply on Dec 5, 2013 by DuckSportsFan

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.

Outcomes