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')

myID   | tSubject_cd  | myGred
--------------------------------------------
1        02             A
1        03             B
1        07             B
1        08             C
null     01             null

myID   | tSubject_cd  | myGred
--------------------------------------------
2        01             C
2        04             B
null     02             null
null     03             null

myID   | tSubject_cd  | myGred
--------------------------------------------
null     01             null
null     02             null
null     03             null

quote:

---

Originally posted by visakh16

why 01 alone came for id=1? what about 02,03,07?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Delinda

plz help me

---

select myID, tSubject_cd, myGred 
from @tMyResult 
where myID = @yourpassedvalue

union all

select null,t.cd,null
from @tSubject  t
where isCompulsory = 1
and not exists (select 1
from @tMyResult
where myID = @yourpassedvalue	
and t.cd = tSubject_cd)