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)

My query:

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?

2 Answers

1
Ripal On Best Solutions

Please try following:

select result.subject_id from student join result on student.id =result.student_id where student.primary_skill in (select primary_skill from student group by primary_skill having COUNT(*)=1)

0
sticky bit On

If I understand this right, you want results for students only having one skill. You can use GROUP BY and a HAVING clause checking for the count of skill being equal to one for this.

SELECT r.subject_id
       FROM result r
            INNER JOIN (SELECT s.student_id
                               FROM student s
                               GROUP BY s.student_id
                               HAVING count(DISTINCT s.primary_skill) = 1) x
                       ON x.student_id = r.student_id;