I've used the ExecuteSQL function for a number of simple queries within a single table and it's working well so far. I now have a more complex query that I would like to see if I can use ExecuteSQL for as the native approach with relationships with multiple predicates is extremely slow and I'm interested in comparing the performance against ExecuteSQL.
Here's my tables:
Contacts - stores the Contact details. If a Contact is considered a Client we set the client field to "Yes". contactID is the primary key field.
Tasks - stores the Tasks assigned to a Contact and a User. Tasks is related to Contacts by contactID = contactID and to Users by userID = userID. The taskCompleted field tracks whether the task has been completed (Yes or No).
Users - stores the User details. userID is the primary key field.
ContactsUsers - a join table between Contacts and Users. Each Contact can be assigned to 1 or more Users. Stores both the contactID and the userID.
I need to do a number of queries, but here's the one I would like to tackle first if possible using ExecuteSQL. For a given user I need to count the number of Tasks for Clients (Contacts flagged as "Yes" for the client field) that have been assigned to them and remove any duplicate contactIDs. For example if John Smith had 10 records with his userID in the ContactsUsers table representing 10 different Contacts, and out of those 10 Contacts 6 were flagged as client = "Yes" in the Contacts table, and out of those 6 Contacts 4 had tasks assigned with John Smith's userID, then the result would be 4 (regardless of how many tasks were assigned to the 4 Contacts).
I've been able to retrieve the unique number Contacts with Tasks assigned to John Smith using the following ExecuteSQL request:
ExecuteSQL ( "SELECT DISTINCT contactID FROM Tasks WHERE userID = ?" ; "" ; "" ; Users::userID )
but this doesn't take into account just the ContactsUsers assigned to John Smith and out of those just the Contacts with client = "Yes".
I'm learning ExecuteSQL as I go so hoping someone with a bit more experience can advise if this is possible and how to format such a request - not sure if I need to use a JOIN here etc or something else?