1 2 Previous Next 20 Replies Latest reply on Apr 16, 2014 11:31 AM by ave

    eSQL help please, with multiple JOINS?

    davehob

      I'm trying to use eSQL for a simple query, but one involving multiple JOINS, which I haven't used before.

       

      The query will find all People whose "searchableData" matches the user input, and who have attended a session of the type selected by the user. (The context is a spotlight search on a layout of Session Details, including a list of possible attenders.)

       

      In FM relationships, it looks like this:

       

      GUI -> PTI -> PGM -> SSN -> ATT -> PPL

       

      where GUI is the globals table in which the user specifies the Programme Title (PTI). PGM = Programmes, SSN = Sessions, ATT = Attendances, and PPL = People. So if I was doing this with a FM Find, I'd just Find all PPL at the "end" of the relationship, and constrain the Find by the search string entered.

       

      Using my basic knowledge of eExecuteSQL, and some resources, especially Beverley Voth's "Missing FM12 ExecuteSQL reference", I've come up with this:

       

      ExecuteSQL("SELECT P.id FROM PPL p, ATT a, SSN s, PGM pg, GUI gu

      WHERE a.id_PPL = p.id

      AND a.id_SSN = s.id

      AND s.id_PGM = pg.id

      AND pg.id_PTI = gu.PTI.ID

      AND searchableData LIKE ? " ; "" ; "" ; "%" & SSN::FILTERINPUT.PEOPLE & "%")

       

      ... but no joy (no results). So I'm obviously doing something wrong, and I would really appreciate it if anyone can tell me what.

       

      Dave.

        • 1. Re: eSQL help please, with multiple JOINS?
          wimdecorte

          No results or a "?"

           

          If you are getting a "?", have you tried something like the SQL Builder to see what the reported error is?

          http://www.modularfilemaker.org/2013/12/query-builder/

           

          A few notes:

          - In your SQL syntas you include GUI but you do not declare a JOIN for it or use in the WHERE clause, so you can scrap that

          - this AND clause is very suspect: AND pg.id_PTI = gu.PTI.ID, the "." in SQL speak is the delimiter between a table and a field.  You can not have more than one "." on either side of the "="

          - in your WHERE clause you specify a field without specifying what table it comes from: AND searchableData LIKE ? , try to avoid that, be explicit about what table it should come from

          - it looks like you also use "." in your field names: don't.  They have a very specific meaning in SQL so stick to a-z,A-Z, 0-9 and the underscore for your TO and field names if you want to have trouble-free SQL use

          • 2. Re: eSQL help please, with multiple JOINS?
            davehob

            Wim,

             

            Thanks very much for this.  Sorry for the lack of clarity - it was an error, not "no results".  I've now removed the "." in the field names, and added the table reference where needed, and sure enough, the error has gone.

             

            BUT, I'm now getting no results returned, so I'm obviously doing something wrong in defining the "relationships" within the query.

             

            I noticed in Beverley's examples, she places the primary key reference on the left of the join, not the right, so I've done that.  Also, although you reckoned I don't need to include GUI at the top, I do use it in the WHERE clause, so I think I still need it?

             

            So this is what I'm left with:

             

            ExecuteSQL("SELECT P.id FROM PPL p, ATT a, SSN s, PGM pg, GUI gu

            WHERE p.id = a.id_PPL

            AND s.id = a.id_SSN

            AND pg.id = s.id_PGM

            AND gu.ID_PTI = pg.id_PTI

            AND p.searchableData LIKE ? " ; "" ; "" ; "%"  & SSN::FILTERINPUT_PEOPLE & "%")

             

            ...and it's not returning the correct results (no records).  Do you have any other suggestions?

             

            Dave.

            • 3. Re: eSQL help please, with multiple JOINS?
              wimdecorte

              The whole construct is a little unclear to me: you're returning only data from PPL, searching only on one field in PPL, so why do you need the joins?

              • 4. Re: eSQL help please, with multiple JOINS?
                user19752

                You are missing PTI table in SQL.

                • 5. Re: eSQL help please, with multiple JOINS?
                  davehob

                  Wim,

                   

                  What I'm trying to achieve is a list of "id"s from PPL, where there is a match on the search field (SSN::FILTERINPUT_PEOPLE) AND in related ATT records (where that relationship is via other tables).  I thought it was the joins which replaced the FM relationship, but I think I have even less understanding of this stuff than I thought!

                   

                  If I'm so wide of the mark that I'm beyond the level of help that you are able to offer, please tell me - I really don't want to waste your time.  And thanks for your pointer to Query Builder, which I've now got working in my solution - that'll be really useful in my learning.

                   

                  Dave.

                  • 6. Re: eSQL help please, with multiple JOINS?
                    wimdecorte

                    Dave Hobson wrote:

                     

                     

                    What I'm trying to achieve is a list of "id"s from PPL, where there is a match on the search field (SSN::FILTERINPUT_PEOPLE) AND in related ATT records

                     

                    is the PPL ID represented as a foreign key in the ATT records?

                    • 7. Re: eSQL help please, with multiple JOINS?
                      davehob

                      I thought I wouldn't need that, as I'm only referring to the foreign key to PTI (Programme Titles) in PGM (Programmes)? 

                      • 8. Re: eSQL help please, with multiple JOINS?
                        davehob

                        Yes, it is.  And the rest of the joins are to identify which ATT (Attendance) recs are relevant, i.e. those pertaining to the Programme Title (PTI) specified by the user.

                        • 9. Re: eSQL help please, with multiple JOINS?
                          wimdecorte

                          I think you're stuck in FM think...

                           

                          If you are asking SQL to return a set of ATT records you need to so the search against the ATT table, not the PPL table.

                           

                          The part that I'm not folliwing is that PTI thing/  Where did the user specify the PTI?  Is it in this field that you are passing along: SSN::FILTERINPUT_PEOPLE?

                           

                          The fact that the user input a PTI programme name in the Globals table (which triggers your FM relationships) does nothing for the SQL query.  It does not honor the existing relationships if that was your thinking.

                          If you want to use that PTI input then it has to show up somewhere in the SQL query.

                           

                          Is the PTI programme name/id represented somewhere on the ATT record?

                          • 10. Re: eSQL help please, with multiple JOINS?
                            davehob

                            No, SSN::FILTERINPUT_PEOPLE holds a search string, entered by the user.  So, in its earlier form, the statement was just:

                             

                            ExecuteSQL("SELECT id FROM PPL

                            WHERE searchableData LIKE ? " ; "" ; "" ; "%"  & SSN::FILTERINPUT_PEOPLE & "%")

                             

                            ... and it worked fine.

                             

                            Then I wanted to filter the list further, to show only those people who matched the search string AND who had attended a certain Programme Title before.  So I now have the user selecting the Programme Title from a list, with the selected id held as GUI::PTI_ID, a global field.   So I don't want to return a list of ATT records - I want a list of PPL ids which have related ATT records.

                             

                            In the hope that a screenshot may help, see below.  So "QuickFind" is where the user enters a search string, and "Lunch Club (Sunday)" is the PTI selection.  Matching People are to be listed as "Possible attenders", who may get added to the list of attenders for this session.

                            attender_list.jpg

                            Sorry for the confusion - and thanks very much for sticking with this.

                             

                            Dave.

                            • 11. Re: eSQL help please, with multiple JOINS?
                              wimdecorte

                              Dave Hobson wrote:

                               

                              So I don't want to return a list of ATT records - I want a list of PPL ids which have related ATT records.

                               

                              You can get the PPL ids from the ATT records, you don't need them from the PPL table.

                               

                              How is the link between ATT and the PTI?  I'm surprised that there is no PTI fk in the ATT because they are attending something, right?

                              • 12. Re: eSQL help please, with multiple JOINS?
                                davehob

                                 

                                How is the link between ATT and the PTI?  I'm surprised that there is no PTI fk in the ATT because they are attending something, right?

                                 

                                Yes, but the link between ATT and PTI is via other tables.  They attend a Session (SSN), which belongs to a Programme (PGM), which has a Programme Title (PTI).

                                 

                                So in Filemaker terms, I would do a find in PPL where PPL_ATT_SSN_PGM_PTI::id (or more likely PPL_ATT_SSN_PGM::id_PTI) equals GUI::ID_PTI, the user's selection.  That's what I'm trying to emulate in eSQL, but you're probably right when you say that I'm thinking too much in FM terms.

                                 

                                Dave.

                                • 13. Re: eSQL help please, with multiple JOINS?
                                  wimdecorte

                                  Understood, so what's the foreign key in ATT that would point to the proper PTI?  Sounds like it is the SSN id.  So the proper sequence would be a number of sql queries, not just one.

                                   

                                  From the chosen Programme name, identify the proper SSNs (I'm guessing it could be multiple).  Then do the query against ATT, without joins but using the IN clause and provide the list of SSN ids there.

                                   

                                  The last query would then become really simple:

                                  SELECT id_PPL from ATT where id_SSN IN (x,y,z)

                                   

                                  To build the query against the SSN table for the chosen PTI, I'd need to know a little more info

                                  • 14. Re: eSQL help please, with multiple JOINS?
                                    davehob

                                    wimdecorte wrote:

                                     

                                    Understood, so what's the foreign key in ATT that would point to the proper PTI?  Sounds like it is the SSN id.  So the proper sequence would be a number of sql queries, not just one.

                                     

                                     

                                    Yes, that's right - there's a foreign key to SSN in ATT, a foreign key to PGM in SSN, and a foreign key to PTI in PGM.

                                     

                                    I really appreciate your help Wim, but it's taken me well beyond my understanding and ability ("a number of queries" - ooh err...), so I need to find time to learn this stuff properly.  I'll report back when I've done so.

                                     

                                    Dave.

                                    1 2 Previous Next