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
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