10 Replies Latest reply on Dec 12, 2014 2:06 PM by uwekrueger

    Need help with JOIN in sql statement

    uwekrueger

      Let ( [

      sql="select count (*) from "Injured People" as ip

       

      join "Project" AS p on p.kp_ProjectId = e.kf_ProjectId

      join "Events" AS e on e.kp_EventNo = ip.kf_EventId

       

      where ip."Injury Classification" = ? and ip."Injury Sub Classification" = 'Statistical' and e.Submitted like 'Yes'

       

      "

      ] ;

      ExecuteSQL ( sql ; "" ; "" ;"FAI" ) & "¶" &

      ExecuteSQL ( sql ; "" ; "" ;"MTI") & "¶" &

      ExecuteSQL ( sql ; "" ; "" ;"RWI" ) & "¶" &

      ExecuteSQL ( sql ; "" ; "" ;"LTI" ) & "¶" &

      ExecuteSQL ( sql ; "" ; "" ;"Fatality" )

      )

      The above is part of dsiplaying graphs. It works if I delete the first join but returns nothing otherwise. I tried various permutations, reading up solutions, etc.

       

      My aim is to replicated the joins between the 3 tables. Project -> Events -> Injured people. The links are based on primary and foreighn keys.

       

      Is there something that I am overlooking?

       

      Any help is appreciated.

        • 1. Re: Need help with JOIN in sql statement
          beverly

          If your RGraph is

           

          Project -> Events -> Injured people

           

          Then FROM ip perspective, you need to flip the two JOINs

           

          FROM ip

          JOIN e

          JOIN p

           

          Also, your "like 'Yes'" has no wildcards, so why did you not use "= 'Yes'"?

           

          -- sent from myPhone --

          Beverly Voth

          --

          • 2. Re: Need help with JOIN in sql statement
            gdurniak

            perhaps a typo in the first Join ?, e.g.

             

            join \"Project\" AS p on p.kp_ProjectId = ip.kf_ProjectId

            join \"Events\" AS e on e.kp_EventNo = ip.kf_EventId

             

            greg

             

            > join \"Project\" AS p on p.kp_ProjectId = e.kf_ProjectId

                join \"Events\" AS e on e.kp_EventNo = ip.kf_EventId

            • 3. Re: Need help with JOIN in sql statement
              uwekrueger

              Thanks Beverly

              I flipped the two joins and it still does not produce the results. The suggestion with the wildcard is good.

               

              The interesting thing is that when I delete the first join (project) it produces a result. As soon as I add the join regardless the order there are no results what so ever.

               

              The reason for the it is to later include a condisiotn in the where clause to only consider projects that match that condition.

               

              I think I am missing something but can't see what.

              • 4. Re: Need help with JOIN in sql statement
                uwekrueger

                Thanks Greg

                 

                I had already checked the joins against the RGraph. Given my typing skills typos are usually the first thing I look for.

                 

                I think it is either simple or my thought process is fundamentally wrong.

                 

                The SQL statement worked in a different context stricting the events to one project via kf_ProjectId.

                 

                What I want to do now is to expand the SQL statement to get a similar out put but for selected projects.

                 

                As said I must be missing something.

                • 5. Re: Need help with JOIN in sql statement
                  user19752

                  So, check e.kf_ProjectId as

                  Let ( [

                  sql="select e.kf_ProjectId from \"Injured People\"  as ip

                   

                  join \"Events\" AS e on e.kp_EventNo = ip.kf_EventId

                   

                  where ip.\"Injury Classification\" = ?  and ip.\"Injury Sub Classification\" = 'Statistical'  and e.Submitted = 'Yes'

                  Group BY e.kf_ProjectId

                  "

                  ] ;

                  ExecuteSQL ( sql ; "" ; "" ;"FAI" ) & "¶" & 

                  ExecuteSQL ( sql ; "" ; "" ;"MTI") & "¶" & 

                  ExecuteSQL ( sql ; "" ; "" ;"RWI" ) & "¶" &

                  ExecuteSQL ( sql ; "" ; "" ;"LTI" )  & "¶" &

                  ExecuteSQL ( sql ; "" ; "" ;"Fatality" )

                  )

                  • 6. Re: Need help with JOIN in sql statement
                    uwekrueger

                    Thanks for this. It does not quite look like what I need but it gives me some good ideas.

                    • 7. Re: Need help with JOIN in sql statement
                      uwekrueger

                      Thanks for all the help

                       

                      Although I am not quite sure what did not quite work, I swapped the JOIN statements around without change, I finally got it working.

                       

                      The final result is:

                      Let ( [

                      sql="select count (*) from \"Injured People\"  as ip

                       

                      join \"Events\" AS e on e.kp_EventNo = ip.kf_EventId

                      join \"Project\" AS p on p.kp_ProjectId = e.kf_ProjectId

                       

                      where ip.\"Injury Classification\" = ?  and ip.\"Injury Sub Classification\" = 'Statistical'  and e.Submitted like 'Yes' and p.\"Project selector for reporting purpose\" like 'Y'

                       

                      "

                      ] ;

                      ExecuteSQL ( sql ; "" ; "" ;"FAI" ) & "¶" & 

                      ExecuteSQL ( sql ; "" ; "" ;"MTI") & "¶" & 

                      ExecuteSQL ( sql ; "" ; "" ;"RWI" ) & "¶" &

                      ExecuteSQL ( sql ; "" ; "" ;"LTI" )  & "¶" &

                      ExecuteSQL ( sql ; "" ; "" ;"Fatality" )

                      )

                      • 8. Re: Need help with JOIN in sql statement
                        user19752

                        It is written in first reply by Beverly.

                        You can't write as

                         

                        select count (*)

                        from \"Injured People\"  as ip

                        join \"Project\" AS p on p.kp_ProjectId = e.kf_ProjectId

                        ...

                         

                        since here is not defined 'e' table yet.

                         

                        You can write tables in any order as using WHERE instead of join

                        FROM .. AS ip, .. AS p, .. AS e

                        WHERE p.kp_ProjectId = e.kf_ProjectId

                        ...

                        But I'm not sure how good FM execute it.

                        • 9. Re: Need help with JOIN in sql statement
                          beverly

                          Again, you are using "LIKE" without any wild cards.

                          Can you tell me why?

                          • 10. Re: Need help with JOIN in sql statement
                            uwekrueger

                            To be honest when I first developed the database I used '='. However, and for no apparent reason it should have worked but it didn't. I tried a few things and when I used LIKE it worked without knowing why. As it was running I did not change it.

                            Similar to issue above. The final solution was one of the permutations that I tried before. It was actually the first one and I kept a copy.

                             

                            This week I thought I had anothe issue with a graph. Whilst a field contained a value it it wasn't displayed

                             

                            At first I thought the data entry was incorrect. Then I downloaded the database from the server. I was surprised to see that the downloaded version worked. I opened the server version and it worked too. I still don't know why.

                             

                            Coming back to the LIKE I will go back and check it again.