10 Replies Latest reply on Nov 20, 2015 4:11 AM by glorifindal

    Execute SQL join tables

    glorifindal

      Hi there,

      been trying to use JOIN in some ExecuteSQL statememts ... and failing miserably.

       

      The table structure looks so ...

      Project

          |

      Service (contains the Project ID)

          |

      ServiceParts (many to many join table - does not contain the Project ID)

          |

      Parts (contains the Project ID)

       

      I am trying to use ExecuteSQL in the following scenario:

      I want to get all the Parts that are related to Service that are also related to the Project.

       

      SELECT id_part from ServiceParts WHERE id_service = $_service.id ;

      then

      SELECT id from Parts WHERE id_part is in $_result (previous query) AND also WHERE id_project = $$_project.id

       

      In FM I would most likely use a CustomList function - but have been ORDERED by the boss to use the ExecuteSQL function from now on.

      My head hurts now.

       

      Can anyone give me an example of this sort of JOIN usage ?

      I would be most thankful.

       

      Kindest regards

       

      G

        • 1. Re: Execute SQL join tables
          user19752

          Simply embed into sub query

           

          ExecuteSQL ("

          SELECT id

          FROM Parts

          WHERE id_part IN (SELECT id_part FROM ServiceParts WHERE id_service =?) AND id_project =?

          " ; "" ; "" ; $_service.id ; $$_project.id )

           

          For joining, we need to know what name the fields have.

          • 2. Re: Execute SQL join tables
            Mike_Mitchell

            glorifindal wrote:

             

            [H]ave been ORDERED by the boss to use the ExecuteSQL function from now on.

             

             

            Um ... why?

            • 3. Re: Execute SQL join tables
              glorifindal

              Why?

               

              I suppose because he is from the SQL world - and does not understand my CustomFunctions and Filemaker stuff

              • 4. Re: Execute SQL join tables
                Mike_Mitchell

                And doesn’t want to bother to learn, I suppose.

                 

                Has anyone bothered to explain the consequences / impacts of this decision? Performance, development time, maintainability ...

                • 5. Re: Execute SQL join tables
                  glorifindal

                  [quote]And doesn’t want to bother to learn, I suppose.[/quote]

                   

                  :-) Seriously ?

                   

                  He has far more important stuff to be getting on with - he runs these multi-million pound projects and is full hands on in most aspects - I am a very small cog indeed in his engine. I do Filemaker, and web, and in the past did some SQL - so it's logical for him to simply say "start using sql"

                  So, I am taking a FM11 solution that was 2 years in the building, and turning it into a FM14 application, this includes using SQL

                   

                  Kindest

                   

                  G

                  • 6. Re: Execute SQL join tables
                    glorifindal

                    Brilliant - of course, sub queries - I forgot about them.

                     

                    Many thanks - excuse the late answer, I'm in Thailand just now and the electricity (as well as the internet) is a bit up-and-down

                     

                    G

                    • 7. Re: Execute SQL join tables
                      wimdecorte

                      glorifindal wrote:

                      so it's logical for him to simply say "start using sql"

                       

                      It may or may not be logical to say it, but it is not logical to use only that.  ExecuteSQL() calls is just one of many tools to achieve the desired end result.  It is NOT the best choice in all scenarios.  The only right approach is to try the different options and pick the one that performs the best.

                       

                      ExecuteSQL() can be extremely fast.  But it will exponentially slow down the more you ask it to do (not linearly, exponentially).  So the more JOINs you add the slower it gets so there definitely is a cross-over point where it does not make sense to use it.

                      • 8. Re: Execute SQL join tables
                        Mike_Mitchell

                        +1

                         

                        Additionally, there are some things that are just better done using native FileMaker functionality. Relational joins, for example, are cached when you first open a file. So if it's on your Graph, duplicating the join in SQL just adds unnecessary overhead. And aggregation in SQL is still quite slow versus using summary fields.

                         

                        You mentioned Custom Functions. Can you even replace that functionality using ExecuteSQL?

                         

                        Anyway, there are a couple of things you can do to help the situation. Make sure you have no open records when you submit the SQL query (props to Wim for that invaluable tip). Use PSoS if appropriate. And try to reason with the boss. 

                        • 9. Re: Execute SQL join tables
                          glorifindal

                          Well, he will leave most stuff to my better judgement - so - if I change something, test it - and it is unbearably slow - then I set it aside, use the original method - and add a note to the upgrade documentation.

                           

                          The sort of stuff I will change to eSQL are the simple queries asked about above.

                          I'm aware that it will simply NOT replace some of the stuff I have already done.

                           

                          Kindest

                           

                          G

                          • 10. Re: Execute SQL join tables
                            glorifindal

                            My boss is very reasonable - he just wants to move forward.

                             

                            But thanks for all the tips -

                             

                            kindest

                             

                            G