8 Replies Latest reply on Oct 30, 2013 12:59 PM by TimAnderson

    ExecuteSQL help please

    TimAnderson

      I am trying to get what must be a fairly standard requirement for SQL but am missing something somewhere, I expect that I am missig something obvious.

       

      The Scenario: We have 6 tables

      Organisations, RequirementGroups, RequirementGroupMenbers, Roles, Assignments and People.

       

      Different Organisations have different requirements, for example Schools need Headteachers, Janitors etc. These are Roles, and Assignments joins Roles, Organisations and People

       

      Eg A school requires a Headteacher, Fred has been assigned the Role of Headteacher for SchoolX

       

      I need to be able, for each Organisation, to be able to get a list of Roles required for that Organisation, and the person assigned to that role. I particularly need to see where there has not been an Assignment for a requied Role.

       

      The code below

       

      ExecuteSQL ( "Select

      Roles.roleName, People.personName

      from Roles

      left join Assignments on Roles.zkUIDp=Assignments.zkRoleUID

      left join People on Assignments.zkPersonUID=People.zkUIDp

      where

      Roles.zkUIDp in (select RequirementMembers.zkRoleUID from RequirementMembers where RequirementMembers.zkRequirementGroupUID=?)

      "

      ; ": ";""; Organisations::zkRequirementGroupUID )

       

      Gets me nearly there, but I cannot work out where to put the 'where Assignments.zkOrganisationUID=?' part to restrict it to a specific Organsiation. The best I have managed to get is a list of only those required Roles ahere an Assignment has been made.

       

       

      Help, as always, much appreciated

       

      Thanks

       

      Tim

        • 1. Re: ExecuteSQL help please
          TimDietrich

          Tim --

           

          I think you need an additional join, to Organisations. Here's how I would handle that query...

           

          ExecuteSQL ( "

          SELECT

                       Roles.roleName,

                       CASE People.personName WHEN NULL THEN 'Unassigned' ELSE People.personName END

          FROM

                       Organisations

                       INNER JOIN RequirementGroupMenbers

                              ( RequirementGroupMenbers.zkUIDp = Organisations::zkRequirementGroupUID )

                    INNER JOIN Roles ON

                              ( Roles.zkUIDp = RequirementGroupMenbers.zkRoleUID )

                    LEFT JOIN Assignments ON

                              ( Assignments.zkOrganisationUID = Organisations.zkUIDp )

                               AND ( Assignments.zkRoleUID = Roles.zkUIDp )

                    LEFT JOIN People ON

                              ( People.zkUIDp = Assignments.zkPersonUID )

          WHERE

                     ( Organisations.zkUIDp = ? )

          "; ": "; ¶; $Organization_UID )

           

          This should give you all of the roles required for the organization (which you can specify using $Organization_UID), and in cases where the role has been filled, you'll see the person's name, and in cases where the role hasn't been filled you'll see "Unassigned."

           

          Also, I wasn't sure if your table "RequirementGroupMenbers" was misspelled or not. It looks like it should be RequirementGroupMembers.

           

          I hope this helps.

           

          -- Tim

          • 2. Re: ExecuteSQL help please
            TimAnderson

            Thanks Tim,

             

            I have tried this (yes, it was a typo in the table name) but am only getting a '?'. I have fixed the '::' in the first inner join

             

            I had tried to craete this extra relationship in a number of ways, and keeep getting the same ?!

             

            This is what I am trying, from your code - I am on the 'Organisations' TO

             

             

            Thanks

             

            ExecuteSQL ( "

             

             

            SELECT

             

             

                         Roles.roleName,

             

             

                         CASE People.personName WHEN NULL THEN 'Unassigned' ELSE People.personName END

             

             

            FROM

             

             

                         Organisations

             

             

                         INNER JOIN RequirementGroupMembers

             

             

                                ( RequirementGroupMembers.zkUIDp = Organisations.zkRequirementGroupUID )

             

             

                      INNER JOIN Roles ON

             

             

                                ( Roles.zkUIDp = RequirementGroupMembers.zkRoleUID )

             

             

                      LEFT JOIN Assignments ON

             

             

                                ( Assignments.zkOrganisationUID = Organisations.zkUIDp )

             

             

                                 AND ( Assignments.zkRoleUID = Roles.zkUIDp )

             

             

                      LEFT JOIN People ON

             

             

                                ( People.zkUIDp = Assignments.zkPersonUID )

             

             

            WHERE

             

             

                       ( Organisations.zkUIDp = ? )

             

             

            "; ": "; ¶; Organisations::zkUIDp )

            • 3. Re: ExecuteSQL help please
              TimAnderson

              By the way, like the 'Unassigned' addition!

              • 4. Re: ExecuteSQL help please
                TimDietrich

                Tim --

                 

                I'm glad you like that "Unassigned" code. Now we just need to get that query working for you!

                 

                When I run into problems like this, I debug them by simplifying the query. I remove most of the joins, and gradually add the joins back as I confirm that they are correct.

                 

                For example, start with this...

                 

                ExecuteSQL ( "

                SELECT

                             *

                FROM

                     Organisations

                     INNER JOIN RequirementGroupMenbers

                          ( RequirementGroupMenbers.zkUIDp = Organisations::zkRequirementGroupUID )

                WHERE

                     ( Organisations.zkUIDp = ? )

                "; ": "; ¶; $Organization_UID )

                 

                That should work. If not, debug it. But if it is okay, then add another join, such as...

                 

                ExecuteSQL ( "

                SELECT

                             *

                FROM

                     Organisations

                     INNER JOIN RequirementGroupMenbers

                          ( RequirementGroupMenbers.zkUIDp = Organisations::zkRequirementGroupUID )

                     INNER JOIN Roles ON

                          ( Roles.zkUIDp = RequirementGroupMenbers.zkRoleUID )

                WHERE

                     ( Organisations.zkUIDp = ? )

                "; ": "; ¶; $Organization_UID )

                 

                 

                ... and so on. Eventually, the problematic JOIN will be revealed.

                 

                -- Tim

                • 5. Re: ExecuteSQL help please
                  TimDietrich

                  Tim --

                   

                  I forgot to add, that when you have all of the joins working properly, you can then specify the specific columns that you want. I think you really only want this:

                   

                       Roles.roleName,

                       CASE People.personName WHEN NULL THEN 'Unassigned' ELSE People.personName END

                   

                  Good luck - and please keep us posted!

                   

                  -- Tim

                  • 6. Re: ExecuteSQL help please
                    TimAnderson

                    Wonderful thanks Tim,

                     

                    got it sorted - I misled you with some table names in my test file.

                     

                    It doesn't seem to like the 'Unassigned' bit though, works fine without it as below, I get the results I hoped for

                     

                    Headteacher: Linda

                    ASL Coordinator:

                    Ed psych:

                    Quality Enhancer: Jeff

                    Janitor: Pete

                     

                     

                    ExecuteSQL ( "

                    SELECT

                                 Roles.roleName,

                             People.personName

                    FROM

                         Organisations

                    INNER JOIN RequirementMembers on

                        ( RequirementMembers.zkRequirementGroupUID = Organisations.zkRequirementGroupUID )

                     

                      INNER JOIN Roles ON

                              ( Roles.zkUIDp = RequirementMembers.zkRoleUID )

                     

                    LEFT JOIN Assignments ON

                     

                                       ( Assignments.zkOrgUID = Organisations.zkUIDp )

                                        AND ( Assignments.zkRoleUID = Roles.zkUIDp )

                     

                            LEFT JOIN People ON

                                          ( People.zkUIDp = Assignments.zkPersonUID )

                     

                    WHERE

                         ( Organisations.zkUIDp = ? )"

                     

                     

                    ; ": "; ""; Organisations::zkUIDp )

                    • 7. Re: ExecuteSQL help please
                      TimDietrich

                      Glad you got it working!

                       

                      For the "Unassigned," give this a try:

                       

                      CASE People.personName WHEN IS NULL THEN 'Unassigned' ELSE People.personName END

                       

                      -- Tim

                      • 8. Re: ExecuteSQL help please
                        TimAnderson

                        found it Tim - a missing 'When'

                         

                        CASE When People.personName IS NULL THEN 'Unassigned' ELSE People.personName END

                         

                        Thanks again for your help