You can add more Join clauses to link People to played and played to instruments. That will allow you to list fields from Instruments along with people.name.
There is more than one "Join" type so I suggest reading up on the options in: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
Thanks, Phil. Do you know the basic syntax for SQL through a join table?
For a relationship like this, I don't see how to handle all three tables:
* * * * * *
Table1 -< JoinTable >- Table2* * * * * *ExecuteSQL ("SELECTidFROMTable1SOME TYPE OF JOINTable2 ON JoinTable.id_Table1 = JoinTable.id_Table2(this section is obviously not right…)WHERETable2.FIELD = 'SEARCHTERM'"; "" ; "" )
Thanks for pointing me in the right direction, Phil. I was finally able to get this to work with a UNION like so:ExecuteSQL ("SELECTidFROMPeopleWHEREPeople.Field1 LIKE '%SEARCHTERM%'ORPeople.Field2 LIKE '%SEARCHTERM%'UNIONSELECTid_PeopleFROMPlayedLEFT JOINInstruments ON Played.id_Instruments = Instruments.idWHEREInstruments.Instrument LIKE '%SEARCHTERM%'"; "" ; "" )