3 Replies Latest reply on Sep 24, 2013 9:20 AM by philmodjunk

    SQL with related tables

    deathrobot

      Title

      SQL with related tables

      Post

           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

      "

           SELECT
                id

           FROM
                Competitrack

           WHERE
                LOWER (adCode) LIKE LOWER (searchTerm)
                     OR
                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.

      Michael

        • 1. Re: SQL with related tables
          philmodjunk

               Using the syntax as written, it looks like you need a JOIN clause defining the relationship between Competitrack and Songs_CompetittrackFilter.

          • 2. Re: SQL with related tables
            deathrobot

                 Hi Phil, and thanks. I'm still confused as to how to do that with a distant table. Something like…

                 SELECT
                      id

                 FROM
                      CompetitrackFilter, Songs_CompetitrackFilter

                 JOIN
                      (something here…)

                 ON
                      (something here…)

                  

                 On the JOIN, can I put "Songs_CompetitrackFilter" even though it is two tables away?

            And for the ON, not sure how that would work. I want to get to Songs_CompetitrackFilter, but the "=" is this:
                 CompetitrackFilter.id = Usage_CompetitrackFilter.id_competitrack (and) 
            Usage_CompetitrackFilter.id_songs = Songs_CompetitrackFilter.id

            Thanks,

            Michael

                  

            • 3. Re: SQL with related tables
              philmodjunk

                   The join has to specify both the tables and the match fields. It records the same info as you have in Manage | Database | Relationships where you have already defined the relationship, but with SQL terms rather than a diagram.

                   You may want to review the SQL examples found in the JDBC ODBC Guide that you can open from FileMaker Help. The Select query examples found in this document are also syntactically correct for use with ExecuteSQL.