7 Replies Latest reply on Mar 19, 2015 3:11 AM by davehob

    eSQL query - results doubled

    davehob

      (I originally added this to an older thread, but it is really a new question, so here it is...)

       

      Just when I thought I'd got the hang of this eSQL thing ...  I now have a new problem with a join query, which I've really tried to figure out, but to no avail.

       

      The relationships are between Sessions (SSN), Programmes (PGM), and Programme Titles (PTI) - i.e. SSN -> PGM -> PTI.

       

      For a group of Sessions, I want to show the number of Sessions for each Programme Title.

       

      Here's the eSQL that I've come up with, based on advice from people here.  (And I'm sorry, Beverly, I haven't renamed my primary keys, yet...).

       

      It works, "kind of".  The problem with it is that the result is doubling the number of Sessions within each Programme Title - in the example below, the results should be Crusoe Club 4, Forget-Me-Not 3, Sunday Club 3, etc.

       

      Any idea what I'm doing wrong?

       

      Thanks,

       

      Dave.

       

      data_viewer.jpg

        • 1. Re: eSQL query - results doubled
          beverly

          Your logic seems to be backwards! what happens when you start FROM PTI as t?

          beverly

          • 2. Re: eSQL query - results doubled
            davehob

            Beverly - with the expression re-ordered (see below) , I get the same (wrong) result.

            Dave.

             

            Let (

            [

            ~idList = Substitute ( SSN::FILTERIDS; "¶"; "','");

            ~query =

            "SELECT t.title, COUNT (s.id)

            FROM PTI AS t

               JOIN PGM AS p ON p.id_PTI = t.id

               JOIN SSN AS s ON s.id_PGM = p.id

            WHERE s.id IN ('" & ~idList & "')

            GROUP BY t.title" ;

            ~result = ExecuteSQL( ~query ; "" ; "" )

            ];

            ~result)

            • 3. Re: eSQL query - results doubled
              beverly

              What if you just use the s.id and not the COUNT? what results do you get? If you are showing double the s.id column, then your COUNT is not unique.

               

              beverly

              On Mar 18, 2015, at 8:07 AM, Dave Hobson <noreply@filemaker.com> wrote in whole or in part

               

               

              eSQL query - results doubled

              reply from Dave Hobson in Discussions - View the full discussion

              Beverly - with the expression re-ordered (see below) , I get the same (wrong) result.

              Dave.

               

              Let (

              [

              ~idList = Substitute ( SSN::FILTERIDS; "¶"; "','");

              ~query =

              "SELECT t.title, COUNT (s.id)

              FROM PTI AS t

                 JOIN PGM AS p ON p.id_PTI = t.id

                 JOIN SSN AS s ON s.id_PGM = p.id

              WHERE s.id IN ('" & ~idList & "')

              GROUP BY t.title" ;

              ~result = ExecuteSQL( ~query ; "" ; "" )

              ];

              ~result)

               

              • 4. Re: eSQL query - results doubled
                nicolai

                Looking at the last query. Let' say you have one session matching one program and matching two Programme titles.

                 

                Counting Session Ids will give you 2 even though they are the same id. You can try using COUNT(DISTINCT s.id) if it is the case.

                 

                And use GROUP BY t.ID just in case you Program Title repeat t some point

                 

                EDIT

                 

                Sorry Beverly, just saw your reply.  I should get out of your way

                • 5. Re: eSQL query - results doubled
                  davehob

                  Beverly - I appreciate your help with this.

                   

                  If I do this...

                  Let (

                  [

                  ~idList = Substitute ( SSN::FILTERIDS; "¶"; "','");

                  ~query =

                  "SELECT COUNT (s.id)

                  FROM SSN AS s

                  WHERE s.id IN ('" & ~idList & "')" ;

                  ~result = ExecuteSQL( ~query ; "" ; "" )

                  ];

                  ~result)

                  ...I get the correct count.

                   

                  If I do this...

                  Let (

                  [

                  ~idList = Substitute ( SSN::FILTERIDS; "¶"; "','");

                  ~query =

                  "SELECT s.id

                  FROM SSN AS s

                  WHERE s.id IN ('" & ~idList & "')" ;

                  ~result = ExecuteSQL( ~query ; "" ; "" )

                  ];

                  ~result)

                  ... I get the correct list of unique ids.

                  But as soon as I introduce joining and grouping, the results are doubled.  So it's obviously my joinery that's at fault, but I'm really stumped.

                  Dave.

                  • 6. Re: eSQL query - results doubled
                    davehob

                    Nicolai - thanks, that did it (DISTINCT), and I think I see why.  I really appreciate your help.

                     

                    Dave.

                    • 7. Re: eSQL query - results doubled
                      davehob

                      Beverly - as you pointed out, the count was the problem, because I was inadvertently telling it to count the Sessions twice.  I really have a lot to learn about this very useful function...

                      Thanks for your input,

                      Dave.