That's where I had started and wasn't having much luck. I might use the keyword surrounded by % wildcards and use LIKE instead of IN. Not sure if there's a performance difference between the two.
ExecuteSQL ( "
SELECT nc_class, nc_class_description
WHERE currently_pulling_hair_out IN (?)
DZ you might get the IN from another query
WHERE xyz IN (SELECT abc FROM ghi WHERE n = ? )
As long as it's the same data type it might work.
Sent from miPhone
In assumes that you have a set of values. I think you are trying to do the type of search where, if this were a FileMaker Find operator, you'd be using the * wild card such as:
*Frustration* in your find request.
If I am right you need to use the Like keyword, not In and use the % operator the way that you would use * in a FileMaker Find request.
Keep in mind that text comparisons are case sensitive so how you capitalize makes a difference as well.
So you might need something like:
Upper ( field ) LIKE %'SEARCHTEXTHERE'%
Using the keyword surrounded by %'s seems to work fairly well.
does that actually work? you might consider passing the parameter:
WHERE abc LIKE %?%
so that the proper quoting happens and it's now dynamic.
% should be in quote, so
Yes, using LIKE in FMP queries will give you an noticeable (negative) performance hit.
Avoid LIKE, if you can, with FMP's SQL.
You do not need LIKE for IN to work (unless you need to wildcard your query).
I have a home database where I track all the vendors who have ever done work. If I want to search for those vendors where the keywords have "tree" or "Gas" (words by themselves in the keywords field), I can do this query:
select vendor, keywords, vendor_rating from vendors where KEYWORDS in ('tree', 'Gas')
However, if I want to find all the vendors where "cooling" (as in air conditioning) is anywhere in the keywords field, I could do this (no IN needed):
select vendor, keywords, vendor_rating from vendors where UPPER(KEYWORDS) LIKE UPPER('%Contractor%')
(above I did an UPPER on both fields to make sure there were no case-sensitive issues -- LIKE IS case sensitive.)
You can also do a sub-query with "IN" as beverly showed you above. Sub-queries are a typical way to do an "IN" clause.
select * from ORDERS a where MAILER_STATE= 'CA' and mailer_state in (Select MAILER_STATE from ORDERS)
(Note that in the query above, the sub-query is completely unnecessary (you wouldn't use it), since the first part of the query does all you need in this case. Typically a sub-query would use values from another table. This was just a simple example, however.)
For a more realistic example where you might use "IN", consider you have two tables:
Each table has belt colors as you might have in martial arts training.
TestSmaller table has these records:
Test table has these records:
Consider the following IN query:
select TestSmaller.belt from testsmaller where belt in (select belt from test)
HOPE THIS HELPS.
Note that sub queries are unfortunately awfully slow in Filemaker's SQL. It seems they're evaluated for each records, which is crazy in most case, where the sub query doesn't depend on a record's filed value).
So you're much better if you do the subquery first and replace it as an IN in the main query
Agreed, it really depends how much data you have. The queries I posted above all worked relatively quickly, that is, "acceptably fast". However, these queries were against tables with only a hundred or so rows. If you have a lot more data, however, be aware.
For example, a very simple LIKE query in FMP against 500,000 records (a table with a single column) takes over 5 seconds, where in MySQL it's 0.22 seconds. Thus for a simple LIKE query, the peformance percent difference is staggering (MySQL is 21 times, or 2,100% faster!). Whether that performance difference makes any difference for a single query would be subjective. But, overall, for my potential projects, SQL performance in FMP is always (currently) a show-stopper. Consider the case where you have multiple users executing queries and they're waiting for over five seconds -- as opposed to nearly instant response. Not good, right?
Hopefully in a some future version of FMP, SQL won't be the "bolted-on" feature it now seems to be and will support UPDATE, INSERT, and DELETE as you would expect in any environment that supports SQL.