[SOLVED] SQL query to get students who have enrolled in English or Urdu course but not in both

SQL query to get students who have enrolled in English or Urdu course but not in both

Query should return Naveed Rizwan Fayaz and Ahmed Name.

SQL query for students who have enrolled in English or Urdu course but not in both.

declare @Student table(sid int identity(1, 1), sname varchar(250))
declare @Course table(cid int identity(1, 1), cname varchar(250))

declare @StudentCourse table(cid int, sid int)
insert into @Student(sname)

select 'Mehboob' union all --1
select 'Rahim' union all -- 2 
select 'Naveed' union all --3 
select 'Rizwan' union all --4
select 'Fayaz' union all --5
select 'Ahmed' -- 6

insert into @Course(cname)
select 'English' union all
select 'Urdu' 


insert into @StudentCourse(sid ,cid)
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,2 union all
select 1,2 union all
select 2,2

Solution

select sid, max(cid)
from StudentCourse 
group by sid
having count(*)=1