CODEWITHSUNDEEP
sqloracle-database
pumang Jha
pumang Jha

Reputation: 39

student enrolled for same courses

Student_table

std_name    course_enrolled
S1           C1
S1           C2
S2           C1
S3           C4
S4           C1
S4           C2
S5           C1

I want to select the student who have enrolled for same course like S1&S4 enrolled for C1&C2, S2&S5 enrolled for C1...like that.This is just a snopshot of table, the actual table will contain more data. Please give me the SQL query for the same.

Upvotes: 2

Views: 961

Answers (2)

arturro
arturro

Reputation: 1606

create table student_enrolled( student varchar2(20),course  varchar2(20) );

insert into student_enrolled values('S1',           'C1');
insert into student_enrolled values('S1',           'C2');
insert into student_enrolled values('S2',           'C1');
insert into student_enrolled values('S3',           'C4');
insert into student_enrolled values('S4',           'C1');
insert into student_enrolled values('S4',           'C2');
insert into student_enrolled values('S5',           'C1');

select * from student_enrolled;

STUDENT              COURSE             
-------------------- --------------------
S1                   C1                  
S1                   C2                  
S2                   C1                  
S3                   C4                  
S4                   C1                  
S4                   C2                  
S5                   C1     



select s1.student , s2.student, s1.course as common_course
from student_enrolled s1 join student_enrolled s2 on (s1.course=s2.course)
where 
-- to not show student with himself
s1.student<>s2.student and
not exists (
  -- all courses enrolled by s2 not enrolled by s1
  select 1 from student_enrolled s3 where s3.student=s2.student and not exists (select 1 from student_enrolled s4 where s4.student=s1.student and s4.course=s3.course) ) 
and 
not exists (
  -- all courses enrolled by s1 not enrolled by s2 
  select 1 from student_enrolled s5 where s5.student=s1.student and not exists (select 1 from student_enrolled s6 where s6.student=s2.student and s6.course=s5.course ) ) 

order by 1,2  ;

STUDENT1             STUDENT2             COMMON_COURSE      
-------------------- -------------------- --------------------
S1                   S4                   C1                  
S1                   S4                   C2                  
S2                   S5                   C1                  
S4                   S1                   C1                  
S4                   S1                   C2                  
S5                   S2                   C1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Using standard SQL, you can do:

with s as (
      select s.*, count(*) over (partition by std_name) as num_courses
      from student_table s
     )
select s1.std_name, s2.std_name
from s s1 join
     s s2
     on s1.std_name < s2.std_name and
        s1.num_courses = s2.num_courses and
        s1.course_enrolled = s2.course_enrolled
group by s1.std_name, s2.std_name
having count(*) = max(s1.num_courses);

This is a rather tricky self-join. But basically it is saying "the number of matching courses is the number of courses that a student is enrolled on."

Upvotes: 0

Related Questions