SQL Statement summarize missing employee certifications

Question

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

Answer 1

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