I am thinking that you are trying to understand how to write the ExecuteSQL statement to grab what you want, and then how to populate a field in the table you want to show, correct? A script can take something like this and set it equal to a variable and then write that to a field. Something like in the picture when you can then populate the fields because of the criteria in you $$globlaField. Imagine (use picture) to think of a scenario where your Activity:: and Company:: tables are not related but you have some global data that relates in some way. That data could be what ExecuteSQL uses to find the data you want in a non-related table.
If you want to produce a set of records to see in list view or a set of records in a a portal, it will be much easier to do that without using SQL in FileMaker, but it is possible to get that result using executeSQL()--the function, not the script step.
For an example of how to use a script to produce a found set without using SQL:Scripted Find Examples
To use ExecuteSQL, to pull up a found set IS possible if you define a relationship to use with that field in Manage | Database | Relationship:
Table1::cESQLField = Table2::PrimaryKey
Your query might be:
ExecuteSQL ("SELECT PrimaryKey FROM Table2 WHERE Table2.Field = 'Apple' " ; "" ; ¶ )
This produces a return separated list of values from PrimaryKey in the cESQLField. You can then use GoToRelatedRecords to pull up the found set of matching records on a layout or a portal to Table2 on your Table1 layout could list those same record. Note that ORDER BY is not a meaningful result in this technique as it will not effect the sort order of the found set of records nor the order of the records shown in the portal.