I have a
project table which contains columns
projectid. The students worked in group to complete the project and a students is allowed to participate in more than one project. I wish to query on how many students have partnered up together on 10 or more project.
How do I do a nested query to find out each unique
studentid have occurrence with other
studentid for more than 10 times?
First self-join the table in order to get the students that worked together. Then aggregate and count.
In order not to look at the same pair twice (s1/s2 <=> s2/s1), I don’t join on s1 <> s2, but on s1 < s2.
select t1.studentid, t2.studentid, count(*) from mytable t1 join mytable t2 on t2.projectid = t1.projectid and t2.studentid > t1.studentid group by t1.studentid, t2.studentid having count(*) >= 10;
Answered By – Thorsten Kettner
Answer Checked By – Mary Flores (BugsFixing Volunteer)