My table and data as following,declare @tSubject table(idx int identity, cd char(2), descrp varchar(100), isCompulsory bit);insert into @tSubject values('01','BM',1)insert into @tSubject values('02','BI',1)insert into @tSubject values('03','MT',1)insert into @tSubject values('04','SJ',0)insert into @tSubject values('05','PI',0)insert into @tSubject values('06','BIO',0)insert into @tSubject values('07','EA',0)insert into @tSubject values('08','MJ',0)declare @tMyResult table(idx int identity, myID int, tSubject_cd char(2), myGred char(1))/*Relationship for @tMyResult to @tSubject is a 1 to many@tMyResult(tSubject_cd) is a foreign key to @tSubject(cd)Combination of myID and tSubject_cd is a unique*/insert into @tMyResult values('1','02','A')insert into @tMyResult values('1','03','B')insert into @tMyResult values('1','07','B')insert into @tMyResult values('1','08','C')insert into @tMyResult values('2','01','C')insert into @tMyResult values('2','04','B')
My expected result as following,if myID=1,myID | tSubject_cd | myGred--------------------------------------------1 02 A1 03 B1 07 B1 08 Cnull 01 null
if myID=2,myID | tSubject_cd | myGred--------------------------------------------2 01 C2 04 Bnull 02 nullnull 03 null
if myID=100, (this scenario is myID not exists in @tMyResult)myID | tSubject_cd | myGred--------------------------------------------null 01 nullnull 02 nullnull 03 null
Hint,1. All @tSubject must be display if isCompulsory=true, but if it exists in @tMyResult don't have to displayI'm stuck to built the SQL for above scenarioReally need help