ExecuteSQL with Multi-key

[jasonwood]

How does one write a SELECT statement to find records with id that match any one id listed in a multi-key field?

 

The following does not work because the contents of the multikeyfield will not entirely equal any individual record id.

 

ExecuteSQL ( "SELECT field FROM table WHERE id=?" ; "" ; "¶" ; multikeyfield )

[Ocean West]

http://fmforums.com/forum/topic/86755-sql-select-with-where-clause-uses-a-checkbox-field/

[beverly]

The multi-key is return delimited. If they are all NUMBERS in the id field, you might try to change the returns to comma and use the IN comparison:

ExecuteSQL ( " SELECT field FROM table WHERE id IN (?) " ; "" ; "¶" ; Substitute(multikeyfield;"¶";',' )

That should make it IN (1,2,3,4,5)

Try it and see.

[jasonwood]

That makes perfect sense to me, but I can't get it to work.

 

I even tried hardcoding it like this in the data viewer:

 

This works (but there's only one value):

 

ExecuteSQL ( " SELECT ancestorsList FROM products_CATEGORIES WHERE id IN (?) " ; "" ; "¶" ; "10036" )

 

But with any of the following, nothing is returned.

 

ExecuteSQL ( " SELECT ancestorsList FROM products_CATEGORIES WHERE id IN (?) " ; "" ; "¶" ; "10036,10039" )

 

with a space after comma:

ExecuteSQL ( " SELECT ancestorsList FROM products_CATEGORIES WHERE id IN (?) " ; "" ; "¶" ; "10036,10039" )

 

with single quotes around each value:

ExecuteSQL ( " SELECT ancestorsList FROM products_CATEGORIES WHERE id IN (?) " ; "" ; "¶" ; "'10036','10039'" )