By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Customers
(Customer_id int,
FName varchar(50),
LName varchar(50))
insert into Customers values
(1,'John','Darwin'),
(2,'Jane','Doe'),
(3,'Bobby','Black')
create table Classes
(Class_id int,
Customer_id int,
ClassType_id int,
ClassName varchar(50),
Status varchar(50))
insert into Classes values
(1,1,1,'Emergency Medical Dispatch v1','Pass'),
(2,1,2,'Emergency Medical Dispatch Instructor','Pass'),
(3,2,3,'Public Safety Telecommunicator','Pass'),
(4,2,1,'Emergency Medical Dispatch v1','Pass'),
(5,2,1,'Emergency Medical Dispatch v2','Fail')
create table ClassTypes
(ClassType_id int,
ClassType varchar(50))
insert into ClassTypes values
(1,'EMD'),
(2,'EMD-I'),
(3,'PST')
11 rows affected
select c.*, cl.*
from customers c
outer apply(
select cl.Class_Id, cl.ClassName, cl.[Status], ct.ClassType
from Classes cl
join ClassTypes ct on ct.ClassType_Id = cl.ClassType_Id
where cl.Customer_Id = c.Customer_Id
and cl.[Status] != 'Fail'
and ct.ClassType != 'PST'
)cl;
| Customer_id | FName | LName | Class_Id | ClassName | Status | ClassType |
|---|---|---|---|---|---|---|
| 1 | John | Darwin | 1 | Emergency Medical Dispatch v1 | Pass | EMD |
| 1 | John | Darwin | 2 | Emergency Medical Dispatch Instructor | Pass | EMD-I |
| 2 | Jane | Doe | 4 | Emergency Medical Dispatch v1 | Pass | EMD |
| 3 | Bobby | Black | null | null | null | null |