AnsweredAssumed Answered

FMSQL - find one-of-many in a field's many?

Question asked by justinc on May 26, 2015
Latest reply on May 27, 2015 by justinc

I have a bit of an SQL question.  I have a list of IDs and I am trying to determine if any of those IDs exist in another field.  This field itself contains lists of IDs, so each record may have multiple IDs to search against.  There are at most 4 IDs that I will be search for in this field.


If I were doing it directly on the table's layout, I would do this:

enter find mode

set FieldA = ID[1]

new record

set FieldA = ID[2]

new record

set FIeldA = ID[3]

new record

set FieldA = ID[4]

Perform find


I am somewhat familiar with SQL's "IN" function, but that seems limited to finding a single value that is in a list of values.  I am trying to find out if any one of a list is in another list.


The whole purpose of this is simply to double check; I have a script that accepts a list of IDs and it is just checking that these IDs don't already exist in some other record in this table, before it adds them as a new record.


Since I only have four possible inputs at the moment, I could just loop through them one at a time and check those results.  But it would be nice to do this in a single step if possible.