3 Replies Latest reply on Apr 5, 2014 10:13 AM by deathrobot

    SQL with multiple tables

    deathrobot

      Title

      SQL with multiple tables

      Post

           Having some conceptual trouble here. I've got this SQL statement which so far works fine. I can type in a search term and it will check in People.name, People.emailAddress, and Company.name:

           ExecuteSQL(
           "SELECT
           People.name
            
           FROM
           People
            
           LEFT JOIN
           Companies ON People.id_Company = Companies.id
            
           WHERE 
           (
           LOWER(People.name) LIKE '%SEARCHTERM%'
           )
           OR
           (
           LOWER(Companies.name) LIKE '%SEARCHTERM%'
           )
           OR
           (
           LOWER(People.emailAddress1) LIKE '%SEARCHTERM%'
           )
           "; ""; "" )
            
           I would also like to check another table that is related to People via a join table:
            
           People ->Played<-Instruments
            
           I'd like to be able to include instruments from the Instruments table when they are related to the person via the Played table. Confused as to how to add this to my current SQL query.
            
           Thanks for any help!
           Michael

        • 1. Re: SQL with multiple tables
          philmodjunk

               You can add more Join clauses to link People to played and played to instruments. That will allow you to list fields from Instruments along with people.name.

               There is more than one "Join" type so I suggest reading up on the options in: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

          • 2. Re: SQL with multiple tables
            deathrobot

                 Thanks, Phil. Do you know the basic syntax for SQL through a join table?

                 For a relationship like this, I don't see how to handle all three tables:

                 * * * * * *

                 Table1 -< JoinTable >- Table2

                 * * * * * *
                  
                 ExecuteSQL ( 
                 "
                 SELECT
                      id
                  
                 FROM
                      Table1
                  
                 SOME TYPE OF JOIN
                      Table2 ON JoinTable.id_Table1 = JoinTable.id_Table2
                 (this section is obviously not right…)
                  
                 WHERE
                      Table2.FIELD = 'SEARCHTERM'
                 "
                  
                  ; "" ; "" )
            • 3. Re: SQL with multiple tables
              deathrobot

                   Thanks for pointing me in the right direction, Phil. I was finally able to get this to work with a UNION like so:

                   ExecuteSQL ( 
                   "
                   SELECT
                   id
                    
                   FROM
                   People
                    
                   WHERE
                   People.Field1 LIKE '%SEARCHTERM%'
                   OR
                   People.Field2 LIKE '%SEARCHTERM%'
                    
                   UNION
                    
                   SELECT
                   id_People
                    
                   FROM
                   Played
                    
                   LEFT JOIN
                   Instruments ON Played.id_Instruments = Instruments.id
                    
                   WHERE
                   Instruments.Instrument LIKE '%SEARCHTERM%'
                    
                   "
                    
                    ; "" ; "" )