sql - how to select unique tuples from same table

Question

I want to find all pairs of diffrent students that are enrolled in two or more sections together from

enroll(sid*, grade, dname*, cno*, sectno*)

where each section is uniquely identified by (dname*, cno*, sectno*)

Answer 1

You can self join on the columns that identify a section and specify e1.sid < e2.sid to fetch each pair of students only once.

select e1.sid, e2.sid from enroll e1
join enroll e2 on e1.dname = e2.dname
and e1.cno = e2.cno
and e1.secno = e2.sectno
where e1.sid < e2.sid
group by e1.sid, e2.sid
having count(*) > 1