I'm using SharePoint Online via Office 365. I have 3 lists:
- Training Certifications
- Training Cert Types
The 3 lists are linked together via lookups. The "Training Certifications" list has a field that's an employee lookup tied to the "Employees" list and another field that's a lookup to the "Training Cert Types" list. The usage would be a user adds a new entry into the "Training Certifications" list and in the process selects an employee and a cert type from their respective lookups. I want to be able to generate some kind of report, a view on the "Training Certifications" list would be ideal, that can show me all the cert types an employee has (this is easy) as well as cert types they do NOT have (this is the question). Keep in mind, it's possible for an employee to have no certs and therefore not even exist in the "Training Certifications" list. This report would then show them multiple times, one for each cert they are missing.
If this were in SQL I could do it in about 60 seconds, but I'm not finding a way to effectively "join" these lists together to get the information I need. Is this possible?
For SQL people here's some pseudo code of what I'm looking for:
select * from [Employees] e full outer join [Training Cert Types] ct on 1=1 left join [Training Certifications] tc on tc.ID = ct.ID and tc.Employee = e.ID