Geek Answers Handbook

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

The request should return Nayved Rizvan Fayaz and Ahmed-Name.

SQL query for students who enrolled in English or Urdu, but not 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
+4
source share
4 answers
 select sid, max(cid) from StudentCourse group by sid having count(*)=1
+1
source

I continued to practice, and finally, I did it on this request, you guys have something else in your mind.

 select s.*, count(1) as TotalEnrolledCourses from @Student s inner join @StudentCourse sc on s.sid = sc.sid where sc.cid in (1,2) group by s.sid, sname having TotalEnrolledCourses = 1
+2
source

Here you can get full information about a student with one registered course i.e. Name and ID, etc.

 SELECT s.* FROM Student s INNER JOIN (select sid, max(cid) from StudentCourse group by sid having count(*)=1 ) sc ON s.sid=sc.sid
+1
source

Another way to solve your problem:

 SELECT s.sid, s.sname FROM @StudentCourse sc INNER JOIN @Student s ON s.sid = sc.sid WHERE sc.cid IN(1, 2) GROUP BY s.sid, s.sname HAVING ( SUM(CASE WHEN sc.cid = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN sc.cid = 2 THEN 1 ELSE 0 END) ) = 1
0
source

More articles:


All Articles