ExecuteSQL help please

Discussion created by TimAnderson on Oct 30, 2013
Latest reply on Oct 30, 2013 by 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


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