I am trying to create a report on tables that I can’t modify and am not sure if this is even possible.
Using the script below, I am trying to get a query result of:
--Certification | Employee | Has Certification --CPR | Santa Clause | Yes --CPR | Rudolph | No CREATE TABLE [dbo].[Certification]([Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_Certification] PRIMARY KEY CLUSTERED ([Id] ASC)); CREATE TABLE [dbo].[Employee]([Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC)); CREATE TABLE [dbo].[EmployeeCertification]([Id] [int] IDENTITY(1,1) NOT NULL, [CertificationID] [int] NOT NULL, [EmployeeID] [int] NOT NULL, CONSTRAINT [PK_EmployeeCertification] PRIMARY KEY CLUSTERED ([Id] ASC)); ALTER TABLE [dbo].[EmployeeCertification] WITH CHECK ADD CONSTRAINT [FK_EmployeeCertification_Certification] FOREIGN KEY([CertificationID]) REFERENCES [dbo].[Certification] ([Id]) ALTER TABLE [dbo].[EmployeeCertification] CHECK CONSTRAINT [FK_EmployeeCertification_Certification] ALTER TABLE [dbo].[EmployeeCertification] WITH CHECK ADD CONSTRAINT [FK_EmployeeCertification_Employee] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([Id]) ALTER TABLE [dbo].[EmployeeCertification] CHECK CONSTRAINT [FK_EmployeeCertification_Employee] INSERT INTO Certification (Name) VALUES ('CPR'); INSERT INTO Employee (Name) VALUES ('Santa Clause'),('Rudolph'); INSERT INTO EmployeeCertification (CertificationID,EmployeeID) VALUES(1,1);
Advertisement
Answer
You can cross join
employees and certifications to generate all possible combinations, then use a subquery to check whether each tuple exists in the bridge table:
select c.name as certification, e.name as employee, case when exists ( select 1 from employeecertification ec where ec.employeeid = e.id and ec.certificationid = c.id ) then 'Yes' else 'No' end as has_certification from employee e cross join certification c
This can also be done with a left join
:
select c.name as certification, e.name as employee, case ec.id is null then 'No' else 'Yes' end as has_certification from employee e cross join certification c left join employeecertification ec on ec.employeeid = e.id and ec.certificationid = c.id