Issue
I have a project table which contains columns studentid, 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.
| studentid | projectid |
|---|---|
| 1 | abx |
| 2 | abx |
| 3 | abx |
| 1 | abn |
| 4 | abn |
How do I do a nested query to find out each unique studentid have occurrence with other studentid for more than 10 times?
Solution
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)