Find 'missing' rows across two result sets

Question

I've been struggling with this for days so apologies if I've over-confused things...

I have a series of tables that define:

1. Groups of skills
2. Skills
3. Skills within the above groups (many-to-many)
4. Job roles
5. The skills with those job roles (many-to-many)

The structure of the database looks like this:

I need to create a result set that displays the following data:

For all of the job roles, show all of the skills within those job roles. However, also include skills within the respective groups that were not included in each role (indicated with NULL or by any other method).

Please see this working code to create the tables and data. SQL Fiddle
Apologies for the length, I did lots of inserts to create a realistic example.

Notice that the PowerPoint skill is not added to the HR Manager role, but other skills from that same group are added. Also notice that the Recruitment Policy skill is not added to the Software Manager role, but I do not need to see this gap because no other skills in that group exist in the role.

The results I would aim for would resemble this (excludes Super Star role for brevity):

RoleTitle               GroupTitle                 SkillTitle               SkillIsInRole
----------------------- -------------------------- --------------------------------------
Software Manager        Microsoft Office           Excel                    1
Software Manager        Microsoft Office           Word                     1
Software Manager        Microsoft Office           PowerPoint               1
Software Manager        Microsoft SQL Server       Query Design             1
Software Manager        Microsoft SQL Server       Stored Procedures        1
Software Manager        Microsoft SQL Server       Failover Clustering      1
HR Manager              Microsoft Office           Excel                    1
HR Manager              Microsoft Office           Word                     1
HR Manager              Microsoft Office           PowerPoint               NULL <-- not added to role but exists in same group as other used skills
HR Manager              HR                         Recruitment Policy       1

Answer 1

Getting all skills for a group related to a role is somewhat simple and is handled in the relatively self explanatory roles cte below. From this the only way I can think of obtaining whether the skill was related 'directly' to the role is by OUTER APPLYING the result set to the result set of actual skills for role.

;WITH skills AS
(
  SELECT g.GroupId, g.GroupTitle, s.SkillId, s.SkillTitle
  FROM @tbl_GroupsSkills gs
  INNER JOIN @tbl_Groups g ON g.GroupId = gs.GroupId
  INNER JOIN @tbl_Skills s ON s.SkillId = gs.SkillId
)
, roles AS
(
  SELECT DISTINCT jr.Id RoleId, jr.RoleTitle, gs.GroupId
  FROM @tbl_jobroles jr
  INNER JOIN @tbl_rolesskills rs ON rs.RoleId = jr.ID
  INNER JOIN @tbl_GroupsSkills gs ON gs.LinkId = rs.LinkId
)
SELECT 
    roles.RoleTitle, 
    skills.GroupTitle, 
    skills.SkillTitle,
    t.SkillIsInRole
FROM skills
JOIN roles ON roles.GroupId = skills.GroupId
OUTER APPLY
(
  SELECT 1 SkillIsInRole
  FROM @tbl_rolesskills rs 
  INNER JOIN @tbl_jobroles r ON rs.RoleID = r.ID 
  INNER JOIN @tbl_groupsskills gs ON gs.LinkID = rs.LinkID 
  INNER JOIN @tbl_groups g ON g.groupID = gs.GroupID 
  INNER JOIN @tbl_skills s ON s.skillID = gs.SkillID
  WHERE s.SkillId = skills.SkillId
  AND g.GroupId = skills.GroupId
  AND r.Id = roles.RoleId
) t
ORDER BY roles.RoleTitle, skills.GroupTitle, skills.SkillTitle

Edit: the OUTER APPLY could be handled with a LEFT JOIN

LEFT JOIN (
  SELECT s.SkillId, g.GroupId, r.Id RoleId, 1 SkillIsInRole
  FROM @tbl_rolesskills rs 
  INNER JOIN @tbl_jobroles r ON rs.RoleID = r.ID 
  INNER JOIN @tbl_groupsskills gs ON gs.LinkID = rs.LinkID 
  INNER JOIN @tbl_groups g ON g.groupID = gs.GroupID 
  INNER JOIN @tbl_skills s ON s.skillID = gs.SkillID
) t ON t.SkillId = skills.SkillId
   AND t.GroupId = skills.GroupId
   AND t.RoleId = roles.RoleId

demo

Answer 2

I think you have to prepare a set of all possible combinations Roles from one side and Group-skills from the other. This is done by making Decart multiplication (usually done by CROSS JOIN). As a result you will have a list of each role combined with a list of all possible Group-skills combinations. After that you can LEFT JOIN this result with table tbl_RolesSkills. This will give what you need. You can do this by using CTE or sub query.

Have to looks like this example

Actually CROSS JOIN is not needed, I missed part with "specific roles have only a specific group sets". Only one sub-query, can be done also with CTE (Common table expression).

I also exclude "Super Star" role. If you want to add it just remove WHERE section.