Using the syntax as written, it looks like you need a JOIN clause defining the relationship between Competitrack and Songs_CompetittrackFilter.
Hi Phil, and thanks. I'm still confused as to how to do that with a distant table. Something like…
On the JOIN, can I put "Songs_CompetitrackFilter" even though it is two tables away?
And for the ON, not sure how that would work. I want to get to Songs_CompetitrackFilter, but the "=" is this:
CompetitrackFilter.id = Usage_CompetitrackFilter.id_competitrack (and) Usage_CompetitrackFilter.id_songs = Songs_CompetitrackFilter.id
The join has to specify both the tables and the match fields. It records the same info as you have in Manage | Database | Relationships where you have already defined the relationship, but with SQL terms rather than a diagram.
You may want to review the SQL examples found in the JDBC ODBC Guide that you can open from FileMaker Help. The Select query examples found in this document are also syntactically correct for use with ExecuteSQL.