declare @tApplyProgram table (myID varchar(50), programID varchar(10), stTR char(1) null)
insert into @tApplyProgram(myID,programID) values('1925','184');
insert into @tApplyProgram(myID,programID) values('4474','172');
insert into @tApplyProgram(myID,programID) values('8890','172');
insert into @tApplyProgram(myID,programID) values('5578','172');
insert into @tApplyProgram(myID,programID) values('2980','172');
insert into @tApplyProgram(myID,programID) values('2500','172');
insert into @tApplyProgram(myID,programID) values('1925','180');
insert into @tApplyProgram(myID,programID) values('5578','180');
/*
@tApplyProgram keep applicant and their programID
myID and programID is unique
*/

declare @tResult table (myID varchar(50), subjectCd varchar(50))
insert into @tResult values('1925','01')
insert into @tResult values('1925','02')
insert into @tResult values('1925','03')
insert into @tResult values('4474','03')
insert into @tResult values('4474','04')
insert into @tResult values('4474','05')
insert into @tResult values('4474','06')
insert into @tResult values('5578','01')
insert into @tResult values('5578','02')
insert into @tResult values('5578','03')
insert into @tResult values('2980','01')
insert into @tResult values('2980','02')
/*
@tResult keep their applicant's result
myID and subjectCd is unique
*/


declare @tRulesD table (programID varchar(50), subjectCd varchar(50))
insert into @tRulesD values('172','03')
insert into @tRulesD values('172','04')
insert into @tRulesD values('172','05')
insert into @tRulesD values('184','01')
insert into @tRulesD values('184','02')
insert into @tRulesD values('184','03')
/*
@tRulesD keep programID rules and regulation
programID and subjectCd is unique
*/

update @tApplyProgram 
set stTR =  
( 
  select 
    case when max (case when u.subjectcd is null then 1 else 0 end)  
            + max (case when r.subjectcd is null then 1 else 0 end) 
            = 0 
         then 1 
         else case when count (u.subjectcd) > 0 
                    and count (r.subjectcd) > 0 
                   then 0  
                   else null  
               end 
 
     end 
    from (select * from @tResult r where r.myid = [@tApplyProgram].myid) r 
    full outer join 
         (select * from @tRulesD u where u.ProgramID = [@tApplyProgram].programid) u 
     on r.subjectCd = u.subjectCd 
) 

myID  | programID  | stTR
------------------------------------
1925	184	         1           /*1925 have rows in @tResult, and 184 have rows in @tRulesD. And, it's meet the requirements */
4474	172	         0           /*4474 have rows in @tResult, and 172 have rows in @tRulesD. And, it's meet the requirements */
8890	172	         NULL        /*8890 don't have rows in @tResult*/
5578	172	         0           /*5578 have rows in @tResult, and 172 have rows in @tRulesD. But, it's not meet the requirement*/
2980	172	         0           /*2980 have rows in @tResult, and 172 have rows in @tRulesD. But. it's not meet the requirement*/
2500	172	         NULL        /*2500 don't have rows in @tResult*/
1925	180	         NULL        /*180 don't have rows in @tRulesD*/
5578	180	         NULL        /*180 don't have rows in @tRulesD*/

myID  | programID  | stTR
------------------------------------
1925	184	         1           /*1925 have rows in @tResult, and 184 have rows in @tRulesD. And, it's meet the requirements */
4474	172	         1           /*4474 have rows in @tResult, and 172 have rows in @tRulesD. And, it's also meet the requirements ~ @tRulesD only need 3 subject's cd, but 4474 have 4 subject's cd. 3 of it is match in @tRulesD   */
8890	172	         NULL        /*8890 don't have rows in @tResult*/
5578	172	         0           /*5578 have rows in @tResult, and 172 have rows in @tRulesD. But, it's not meet the requirement*/
2980	172	         0           /*2980 have rows in @tResult, and 172 have rows in @tRulesD. But. it's not meet the requirement*/
2500	172	         NULL        /*2500 don't have rows in @tResult*/
1925	180	         NULL        /*180 don't have rows in @tRulesD*/
5578	180	         NULL        /*180 don't have rows in @tRulesD*/