CODEWITHSUNDEEP
sql
user3686599
user3686599

Reputation: 13

SQL query to find out the single record by 3 conditions

Can you please help me with the query to find out the single record for the each student based on the subject.(Id,Name,subs are primary key). Ideally I will have 2 records for each subject. One with school TTT and MCC.

Table Structure:

StudentID   StudentName Subject Marks   School
    1          AAA        ENG      80      TTT
    1          AAA        ENG      80      MCC
    1          AAA        TAM      90      TTT
    1          AAA        TAM      90      MCC
    2          BBB        TAM      90      TTT
    2          BBB        TAM      90      MCC
    3          CCC        ENG      40      MCC
    4          DDD        ENG      95      MCC
    5          EEE        ENG      85      TTT
    5          EEE        ENG      85      MCC

I want the results 

StudentID   StudentName Subject Marks   School
    3           CCC       ENG      40      MCC
    4           DDD       ENG      95      MCC

Upvotes: 0

Views: 211

Answers (3)

GMB
GMB

Reputation: 222402

You can filter with a correlated subquery:

select t.*
from mytable t
where (select count(*) from mytable t1 where t1.studentid = t.studentid) = 1

Upvotes: 1

Avi
Avi

Reputation: 1845

SELECT * FROM TABLEA A 
WHERE EXISTS (SELECT STUDENTid, COUNT(1) TOTAL FROM TABLEA B 
                  WHERE A.STUDENTID = B.STUDENTID      
                 GROUP BY STUDENTID 
                 HAVING COUNT(1) = 1)

This will give records to cases where there is no duplication of IDs

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Here is one method using window functions:

select t.*
from (select t.*,
             count(*) over (partition by studentid) as cnt
      from t
     ) t
where cnt = 1;

This returns students with only one record. If you want student/subject combination with only one record, another method uses not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.studentid = t.studentid and
                        t2.subject = t.subject and
                        t2.school <> t.school
                 );

Upvotes: 1

Related Questions