11 Replies Latest reply on Mar 22, 2016 3:25 AM by davehob

    Structuring a multi-join eSQL query

    davehob

      I’m using eSQL and a virtual list to do some really useful analysis of activities in the charity where I work.  But now I’m stuck.

      The basic data structure is thus:

       

      PTI > PGM > SSN  - a Programme Title (PTI) can have many Programmes (PGM), and a Programme can have many Sessions (SSN).

      By using various eSQL queries, I’m populating a virtual list, which gets displayed in a portal. To get, for example, the number of Sessions within a Programme Title, I’m doing this:

      count_ssns.JPG

      And to get the total no. of Programmes within a Programme Title, I’m doing this:

      count_pgms.JPG

      These work fine, so I’m happy that the eSQL syntax is OK.  Note that the set of Sessions under scrutiny is held in SSN::FILTERIDS (which has to be converted to a comma-separated list), and the results need to be grouped by Programme Title.

       

      What I need to do now is to get some counts from other related tables.  And this is where I’m stuck.  Attendances to a Session are held in an Interaction table (INT), which is in turn related to People (PPL).  So the data structure of this bit is  SSN > INT < PPL (i.e. the INT table is a join table).

       

      And what I need now is, for example, the number of individual People attending a group of Sessions.  (Note that this is not the same as the number of Attendances, as a person may have attended more than one Session.)  I will then need other counts from the PPL table (no. of disabled people, etc.).

       

      If you can give me any pointers as to how to structure the query, I’d be very grateful.  Everything I've tried so far yields the dreaded '?' (but I must admit to a degree of desperate guesswork!).

       

      Thanks for reading,

       

      Dave.

        • 1. Re: Structuring a multi-join eSQL query
          rrrichie

          Hi Dave,

           

            SELECT  COUNT (PPL.id) FROM

                PPL

                JOIN INT ON PPL.id = INT.id_PPL

           

            WHERE INT.id_SSN IN ( session list )

           

            if you know the key of the sessions, and i presume they are in the INT table, then you don't do join SSN at all.  Unless you need extra info from the SSN table...  But may be first try this construct to see if you at least get the people you are looking for :-)

           

          Happy coding!

          • 2. Re: Structuring a multi-join eSQL query
            beverly

            DISTINCT gives you unique values. But my question is if you need the resulting value interspersed with your current queries or stand alone in another virtual list?

             

            beverly

            • 3. Re: Structuring a multi-join eSQL query
              davehob

              Thanks Richie,

               

              That's very helpful, and yes, it does return the correct people count.  However, it's very slow to do so (unacceptably so), and I still need to build in the additional joins to group by Programme Title, as in the other examples, which will only make it slower.  So I think I must have issues with my data - one step forward and another one back! 

               

              But thanks for helping me to see how to get the People count.

               

              Dave.

              • 4. Re: Structuring a multi-join eSQL query
                davehob

                Hi Beverly.  The People count needs to be amongst other values.  It's currently a 6 column Virtual List:

                • Label - i.e. the Programme Title
                • Tot 1 - no. of sessions
                • Tot 2 - no. of attendances
                • Tot 3 - no. of people
                • Tot 4 - no. of anonymous attendances
                • Tot 5 - no. of frequent attenders

                Here's the portal so far, displayed on a popover:

                analysis_popover.JPG

                Please let me know if there's a better way to do this!

                Dave.

                • 5. Re: Structuring a multi-join eSQL query
                  beverly

                  if it needs to be separate queries (and it may), then I wonder if there's a possibility to plug into the Virtual List from two (or more) different variables (set by ExecuteSQL).

                   

                  Or, I wonder if it's possibly a nested SELECT (one of the columns would get the added count). But that can get overly complex and really bog down.

                   

                  Or, I wonder if you have your Virtual List so far and add a calculation field (or set field with loop) based on the values in each "virtual" record - and using ExecuteSQL.

                   

                  We don't have temp tables and query-a-query in FMP like in SQL, so we improvise (aka work-around).

                   

                  I just don't know all the variables. Think about the possibilities here and you may need to combine a few techniques for sake of speed.

                  beverly

                  • 6. Re: Structuring a multi-join eSQL query
                    user19752

                    I'm not sure but, s.id should be primary key and it has only one s.id_PGM as foreign key for each s record, same as PTI and PGM, so why do you need DISTINCT in COUNT() ?

                    • 7. Re: Structuring a multi-join eSQL query
                      user19752

                      This seems old problem which not resolved eSQL - need a DISTINCT, but not sure where?

                      • 8. Re: Structuring a multi-join eSQL query
                        rrrichie

                        Hi Dave,

                         

                        Maybe a two step approach.  Because you could just use the INT table and retrieve the keys of the people and you know the SSN keys already.

                         

                          Then do a separate query (or a FileMaker relation) to retrieve people data and programme title.

                         

                          How is the speed if you just do a

                         

                          SELECT INT.id_PLL FROM INT WHERE INT.id_SSN IN (......)

                         

                          You could probably make it faster if you can use a date range and it would be helpful if the date was also in the INT table.  This would mean storing the SSN date in the session as well as INT, but if you can use a date range that way, it will save a join and speed things up greatly.

                         

                          Also some one mentioned to me, that getting the keys from SQL and then doing a value count of the resulting list is faster.

                         

                        Ramon.

                        • 9. Re: Structuring a multi-join eSQL query
                          davehob

                          Thanks - the other issue that I had was with the same analysis function, but relating to different data.  This other problem was not resolved using eSQL - I couldn't get it to work (my inadequacy, I'm sure...), so I went for a native FM solution, which is OK, albeit with the drawbacks of needing additional relationships and summary fields.  Beginning to think that's the way to go here.

                          • 10. Re: Structuring a multi-join eSQL query
                            rrrichie

                            How about creating a aggregated INT table with all the info in there with a  nite schedule.  Past attendances won't change, so it's easy to create a table with totals from past months or week or even day.  If you need up to date info you can use a UNION operator to do a construct like this.

                             

                            SELECT program_title, tot_sessions, tot_attendances FROM INT_Aggregated WHERE program+key is 123

                             

                            UNION

                             

                            SELECT .... put the join query here from active tables for the current day or week i.e. .  Just make sure the order, number of columns and type is the same.

                             

                            Ramon

                            • 11. Re: Structuring a multi-join eSQL query
                              davehob

                              Ramon,

                               

                              Thanks for this suggestion.  You're absolutely right, the data won't often change, so this fits well with my strategy of avoiding doing calculations 'on the fly' where it can be avoided.

                               

                              Thanks again (and I'm sorry for the late reply - I've ben away),

                               

                              Dave.