I'm trying to write sql query which return me subject_id(result table) where primary_skill(student table) is unique.
Result table has column (student_id, subject_id, mark)
SELECT r.subject_id FROM result r JOIN student s ON r.student_id = s.student_id WHERE s.primary_skill IN (SELECT DISTINCT primary_skill FROM student) GROUP BY 1;
I have this result:
subject_id 1 2 3 4 5 6 7 8 9 10 1001
But I should return only id 1001, because only this subject has unique student primary_skill, in other ids primary skill are repeated.
What am I doing wrong? How it improve?