5 Replies Latest reply on Jun 19, 2012 2:46 PM by philmodjunk

    selfjoin sql

    FH

      Title

      selfjoin sql

      Post

      Hi,

      how do I express a selfjoin in SQL using Filemakers 12 internal SQL syntax?

       

      eg. table called 'school' with a selfjoin matching 'names' and not matching (unequal) 'ID'

       

      Thanks in advance,

       

      Francis

        • 1. Re: selfjoin sql
          philmodjunk

          You can find an example of the needed SQL for a self join in the ODBC JDBC guide. (I've confirmed with someone at Filemaker the the select statement syntax documented here also applies to Execute SQL.)

          If I've decoded it correctly, You can use

          FROM School A Join School B WHERE A.Name = B.Name AND A.ID <> B.ID

          • 2. Re: selfjoin sql
            FH

            Hi Phil,

             

            thanks for your comment, but I wasn't able to get any results with your proposal of using the JOIN

             

            In the FM ODBC documentation, I found  this example for a self join, without using JOIN which only has one condition

            SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id

             

            Also using the query as explained here, doesnt work: http://www.w3resource.com/sql/joins/perform-a-self-join.php

            SELECT *  FROM company a, company b  WHERE a.company_city=b.company_city  AND a.company_name<>b.company_name

            I only get results for leaving out the unequal condition A.ID<>B.ID, like that:

            SELECT * FROM School A, School B WHERE A.Name = B.Name 

            But of course that's not what I'm looking for.

             

            It might be, that there is a problem with the unequal condition ...

            • 3. Re: selfjoin sql
              FH

              Turns out, that in my example I had the primary key named _pk and SQL doesn't like the leading underscore.

              • 4. Re: selfjoin sql
                FH

                Further fine tuning: These two queries give identical and expected results for a self join

                 

                SELECT B.pk FROM school A JOIN school B ON A.Name=B.Name WHERE A.pk<>B.pk
                
                SELECT B.pk FROM school A, school B  WHERE  A.Name=B.Name AND A.pk<>B.pk
                 

                I wonder if there are any SQL cracks among us who can tell what's the difference, if there is any.

                 

                 

                Edit: in fact, the statements above return all possible matches independent of the currently selected record, but we can modify the statement using an additional argument (school::pk) like below and it works as if we would look through a portal:

                ExecuteSQL ( "Select .pk FROM school A JOIN school B ON A.Name=B.Name WHERE A.pk<>B.pk AND A.pk=?";"";"";school::pk)

                 

                 

                To go even further it is possible to use a FM variable with the ExecuteSQL command e.g.

                Let (field="Name";
                ExecuteSQL ( "Select .pk FROM school A JOIN school B ON A."& field &"=B."& field &" WHERE A.pk<>B.pk AND A.pk=?";"";"";school::pk)
                )


                • 5. Re: selfjoin sql
                  philmodjunk

                  We are all fumbling our way through the use of this function based on very incomplete documentation. A quick check shows that the correct syntax for my example would have been:

                  FROM School A INNER JOIN School B ON A.Name = B.Name AND A.ID <> B.ID

                  As I look at your examples, I'm wondering if you really need any join here. Wouldn't this query work for you?

                  ExecuteSQL ( "Select * from School Where School.name = ? AND School.ID <> ?" ; Char ( 9 ) ; ¶ ; School::Name ; School::ID )

                  That pulls up tab delimitted fields with records separated by returns for all records in School that have the same value in Name but not the same ID as the current record.

                  And it does confirm that <> works as the "does not equal" operator where  ≠  does not.