AnsweredAssumed Answered

SQL with related tables

Question asked by deathrobot on Sep 23, 2013
Latest reply on Sep 24, 2013 by philmodjunk


SQL with related tables


     I am using SQL to populate portals based on a global search field. Everything is working great, except for one layout where I need to have the WHERE statement sometimes reference a field in a related table. I've been struggling with this, but can't seem to get the syntax right. Here's the whole statement, but the trouble is with the last two AND statements that deal with Songs_CompetitrackFilter.ascapID. This is a field in a related table two levels away:

     CompetitrackFilter ---< Usage_CompetitrackFilter >--- Songs_CompetitrackFilter




          LOWER (adCode) LIKE LOWER (searchTerm)
          LOWER (advertiser) LIKE LOWER (searchTerm)


     Case (
          Competitrack::FILTER.TRACKSTATUS = "All Status";
          Competitrack::FILTER.TRACKSTATUS = "Submitted";
               " AND NOT id_ASCAPInquiries IS NULL ";
          Competitrack::FILTER.TRACKSTATUS = "To Submit";
               " AND id_ASCAPInquiries IS NULL  AND NOT Songs_CompetitrackFilter.ascapID IS NULL ";
     Competitrack::FILTER.TRACKSTATUS = "Not Ready";
          " AND id_ASCAPInquiries IS NULL  AND Songs_CompetitrackFilter.ascapID IS NULL "


Thanks for any help with this.