CODEWITHSUNDEEP
sqlpostgresql
Mefisto_Fell
Mefisto_Fell

Reputation: 916

How to get value from one table based on distinct values from another table?

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?

Upvotes: 0

Views: 1570

Answers (2)

RVP
RVP

Reputation: 118

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)

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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;

Upvotes: 0

Related Questions