declare @tDirMovie table
(cd varchar(50), descrp varchar(200), isCompulsory bit)
/*cd is a unique*/

insert into @tDirMovie values('01','mission impossible', 'true')
insert into @tDirMovie values('02','thor', 'false')
insert into @tDirMovie values('03','x-men', 'true')
insert into @tDirMovie values('04','batman', 'true')
insert into @tDirMovie values('05','hulk', 'false')


declare @tMyMovie table
(myID varchar(10), tDirMovie_Cd varchar(50))
/*@tMyMovie(tDirMovie_Cd) is a foreign key to @tDirMovie(cd)*/

--if myID='1925'
insert into @tMyMovie values('1925','01')
insert into @tMyMovie values('1925','02')

--if myID='4474'
insert into @tMyMovie values('4474','01')
insert into @tMyMovie values('4474','03')
insert into @tMyMovie values('4474','04')

if (.....)
Begin
 print 'The result is all movie with IsCompulsory='true' is SELECTED'
End
else
Begin
 print 'The result is all movie with IsCompulsory='true' is NOT SELECTED'
End

if exists(select 1
          from @tDirMovie d
          inner join @tMyMovie m
          on m.tDirMovie_Cd = d.Cd
          where m.myID = @youridvalue
          group by myID 
          having sum(case when isCompulsory ='false' then 1 else 0 end) >0
Begin
 print 'The result is all movie with IsCompulsory='true' is NOT SELECTED'
End
else
Begin
print 'The result is all movie with IsCompulsory='true' is SELECTED'
End