How to compare multiple records in multiple rows for the same ID?

Problem: I'm trying to see, per classID, if an employee (empID) has at least 1 StateCertArea in the FedCertArea, and flag = 1 if so.

Background knowledge on this fake case:

All classID's have FederalCertAreas
All employee's have StateCertAreas
I'm trying to see if an employee has a StateCertArea that matches any FedCertArea for the ClassID.

I need to avoid a SELECT in a CASE statement if possible. Maybe using some temp tables and a join, but I'll take the help I can get.

DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable ( ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))

INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')

SELECT t.ClassID
     , t.EmpID
     , t.StateCertArea
     , t.FedCertArea      
     --, CASE WHEN 'EmployeeID has at least 1 StateCertArea in FedCertArea' THEN 1 ELSE 0 END AS IsInArea    
FROM #temptable AS t

Expected Result:

Solution

An outer apply provides an efficient way to run correlated subqueries such as the one needed here e.g:

DROP TABLE IF EXISTS #temptable
CREATE TABLE #temptable (ClassID int, EmpID decimal(10,0), StateCertArea varchar(4), FedCertArea varchar(4))

INSERT INTO #temptable (ClassID, EmpID, StateCertArea, FedCertArea)
VALUES
( 572888, 77777, '228', '228'),
( 572888, 77777, '389', '389'),
( 572888, 77777, '374', '374'),
( 222555, 77777, '333', '999')

SELECT DISTINCT
       t.ClassID
     , t.EmpID
     , CASE WHEN oa.HasMatchingArea = 1 THEN 1 ELSE 0 END AS IsInArea    
FROM #temptable AS t
OUTER APPLY (
   SELECT TOP(1) 1 AS HasMatchingArea
   FROM #temptable AS t2
   WHERE t2.ClassID = t.ClassID
     AND t2.EmpID = t.EmpID
     AND t2.StateCertArea = t.FedCertArea
) AS oa

ClassID	EmpID	IsInArea
222555	77777	0
572888	77777	1

fiddle

alternate using coalesce

SELECT DISTINCT
       t.ClassID
     , t.EmpID
     , COALESCE(oa.HasMatchingArea,0) AS IsInArea    
FROM #temptable AS t
OUTER APPLY (
   SELECT TOP(1) 1 AS HasMatchingArea
   FROM #temptable AS t2
   WHERE t2.ClassID = t.ClassID
     AND t2.EmpID = t.EmpID
     AND t2.StateCertArea = t.FedCertArea
) AS oa