There are a ton of posts about using ExecuteSQL to perform queries on multiple tables. I've read a lot of Beverly Voth's posts and writing, which is great. Yet I still can't seem to get working what I want to work.
I won't go into painful detail about exactly what I'm trying to do, but I'll give a generalization and hoping someone can tell me, "yes, you can do this with ExecuteSQL" or "no".
I’m trying to construct a query that finds a list of IDs in one table based off a field value in another table. Both tables share a common field value to join on.
Ultimately, I need a list of UUIDs from Table #1. I’m getting this list using SQL and my criteria is that I only want records where the “dateLastSold” in Table #2 is greater than x. (there is actually some other criteria based on fields in Table #1 but no sense complicating the example)
Giving more detail in case it helps : both tables contain the exact same Products. The difference is that each table contains different fields about the products. For instance, itemDescription is in Table1 and dateLastSold is in Table2. I need to gather a list of the UUIDs of the Table1 records, but I only need those records where the dateLastSold of the item is greater than x date. And dateLastSold is in the Table2.
Can ExecuteSQL and JOIN handle this kind of relationship query?