Sql Table Join help

,
1

I have 2 tables .

tblStudentMarksOriginal -
StudID, Subject, Marks
101, english, 91
101, maths, 76
101, science, 89

tblStudentMarksCopy1 -
studID, English, maths, science
101, 91, 76, 89

I need to write sql query to join both tables to check if marks match or not for each student subjectwise. If match then display matching else display nomatch. Results can be displayed in any format- column wise or row wise.

3

hi

hope this helps

create data script

drop table #tblStudentMarksOriginal
create table #tblStudentMarksOriginal(stuid int , subject varchar(100) , marks int )
insert into #tblStudentMarksOriginal select 101, 'English',91
insert into #tblStudentMarksOriginal select 101, 'Maths',71
insert into #tblStudentMarksOriginal select 101, 'Science',89

select * from #tblStudentMarksOriginal

drop table #tblStudentMarksCopy1
create table #tblStudentMarksCopy1(stuid int , English int , Maths int ,Science int )
insert into #tblStudentMarksCopy1 select 101,91,76,89
select * from #tblStudentMarksCopy1

; with cte as 
   ( 
        select stuid,english as MarksCopy,'English' as subject from  #tblStudentMarksCopy1 
            union all
        select stuid,maths,'Maths' from  #tblStudentMarksCopy1 
            union all 
        select stuid,science,'Science' from  #tblStudentMarksCopy1   
    ) 
select 
     a.*
  ,  b.MarksCopy
  ,  case when 
         a.marks = b.MarksCopy then b.subject +  ' Marks Matching'  
     else 
	     b.subject +  ' Marks NOT Matching'   
	 end 
from 
    #tblStudentMarksOriginal a  join cte b   on a.stuid = b.stuid and a.subject = b.subject

image