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)
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.