FileMaker just takes the Table as it is. If you want to query a table from another ODBC source
If you want to filter your ESS tables in FileMaker I suggest you use Trigger for OnLayoutLoad to show the records you want to show
Using ESS, you are looking at the data in the external tables and can use any of the FileMaker functionality on the data (with permissions and other caveats). Relationships are possible (your JOINs).
There is no "LIKE", except FileMaker has some really good "wildcards":
Because the default is "begins-with" (any word in the field), you can use that fact and any of the symbols to get really good finds.
There is also no "IN", but FileMaker has a very clever little functionality called "Multi-line key". This is a return delimited list and typically a global field that can be indexed and used as a 'Primary key' to match any (OR find) of the values in the list with any records' key (whatever field matches). With multiple find criteria this kind of key isn't so helpful.
Scripted finds are recommended so you can easily edit and make "AND" requests or "OMIT" requests. There are also Constrain and Extend find functions & script steps.
You can save values (from elsewhere) into variables that can be used with the scripted finds:
Set Field ( my field ; $myVar )
There is also ExecuteSQL() function which may be able to perform this query on your ESS table(s). You get back a text field with tab-delimited columns and return-delimited rows (or other delimiters). It's not a temp-table like SQL, but many have found ways to "parse" and put into a virtual table. With large datasets and/or complex joins, the usage of ExecuteSQL() may be prohibitive.
If you have ESS and can get a VIEW of the query, you can open the view as if another table (with all you values from the query). You need to have control of the SQL server or have good IT that can create the view for you.
FM & SQL db administrator