1 2 Previous Next 27 Replies Latest reply on May 19, 2017 12:27 PM by wfgclapp

    SQL query based on related table

    wfgclapp

      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?

       

      Thanks!

      Martin

        1 2 Previous Next