IF OBJECT_ID('TempDB..#MathScience','U') IS NOT NULLdrop table #MathSciencecreate table #MathScience( StudentID int, SubjectChoice varchar(10))insert #MathScienceselect 1, 'Math' union allselect 1, 'Science' union allselect 2, 'Science' union allselect 3, 'Math'select StudentID, Case when COUNT(*) > 1 then 1 else 0 end as Markerfrom #MathSciencegroup by StudentIDhaving COUNT(*) > 1 or MIN(SubjectChoice) = 'Math'

select StudentID, Case when COUNT(*) > 1 then 1 else 0 end as Markerfrom #MathSciencewhere StudentID in (select StudentID from #MathScience where SubjectChoice = 'Math')group by StudentID

IF OBJECT_ID('TempDB..#NewMathScience','U') IS NOT NULLdrop table #NewMathSciencecreate table #NewMathScience( StudentID int, Name varchar(20), SubjectChoice varchar(20), SelectionDate datetime)insert #NewMathScienceselect 10021, 'john', 'Math', '2001-11-01' union allselect 10021, 'John', 'Science', '2011-11-12' union allselect 10022, 'Becky', 'Math', '2011-11-02' union allselect 10022, 'Becky', 'Science', '2011-11-10' union allselect 10023, 'Will', 'Math', '2011-11-04' union allselect 10024, 'Matt', 'Math', '2011-11-05'select StudentID, Name, Case when COUNT(*) > 1 then 1 else 0 end as Markerfrom #NewMathSciencegroup by StudentID, Namehaving COUNT(*) > 1 or MIN(SubjectChoice) = 'Math'select StudentID, Name, Case when COUNT(*) > 1 then 1 else 0 end as Markerfrom #NewMathSciencewhere StudentID in (select StudentID from #NewMathScience where SubjectChoice = 'Math')group by StudentID, Name