CODEWITHSUNDEEP
sqlselect
wiwo
wiwo

Reputation: 883

SQL - select students that had exams on every subject

I have two tables: Exams(StudentID, SubjectID), Subjects(SubjectID)

And want to select students that had exam on all subjects. How to do it? Is it possible without using GROUP and COUNT?

Upvotes: 2

Views: 2822

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can do it in many ways. One of those:

DECLARE @students TABLE ( id INT )
DECLARE @exams TABLE ( id INT )
DECLARE @studentexams TABLE
    (
      studentid INT ,
      examid INT
    )

INSERT  INTO @exams
VALUES  ( 1 ),
        ( 2 )
INSERT  INTO @students
VALUES  ( 1 ),
        ( 2 ),
        ( 3 )
INSERT  INTO @studentexams
VALUES  ( 1, 1 ),
        ( 1, 2 ),
        ( 2, 1 )


SELECT  *
FROM    @students s
WHERE   NOT EXISTS ( SELECT *
                     FROM   @exams e
                     WHERE  e.id NOT IN ( SELECT    se.examid
                                          FROM      @studentexams se
                                          WHERE     se.studentid = s.id ) )

Output:

id
1

Upvotes: 2

JohnLBevan
JohnLBevan

Reputation: 24430

Is this what you're after:

--list all students
select * 
from @students st
--where there isn't
where not exists
(
--any subject
    select top 1 1 
    from @subjects su 
--for which that student did not take an exam
    where su.id not in 
    (
        select subjectid 
        from @exams e 
        where studentId = st.id
    )
)

Here's the full code (i.e. including sample data tables):

declare @subjects table(id bigint not null identity(1,1), title nvarchar(32))
declare @students table(id bigint not null identity(1,1), name nvarchar(32))
declare @exams table(id bigint not null identity(1,1), studentId bigint, subjectId bigint, grade nchar(1), attempt int)


insert @subjects select 'Maths' union select 'English' union select 'Geography' union select 'Computer Science'
insert @students select 'Anna' union select 'Billy' union select 'Christie' union select 'Daniel'
insert @exams select st.Id, su.Id, grade, attempt
from
(
    select 'Anna' student, 'Maths' subject, 'A' grade, 1 attempt
    union select 'Anna' student, 'English' subject, 'A' grade, 1 attempt
    union select 'Anna' student, 'Geography' subject, 'A' grade, 1 attempt
    union select 'Anna' student, 'Computer Science' subject, 'A' grade, 1 attempt
    union select 'Billy' student, 'Maths' subject, 'A' grade, 1 attempt
    union select 'Billy' student, 'Computer Science' subject, 'A' grade, 1 attempt
    union select 'Christie' student, 'Maths' subject, 'A' grade, 1 attempt
    union select 'Christie' student, 'English' subject, 'F' grade, 1 attempt
    union select 'Christie' student, 'English' subject, 'E' grade, 2 attempt
    union select 'Christie' student, 'English' subject, 'A' grade, 3 attempt
    union select 'Daniel' student, 'Maths' subject, 'A' grade, 1 attempt
    union select 'Daniel' student, 'English' subject, 'A' grade, 1 attempt
    union select 'Daniel' student, 'Geography' subject, 'A' grade, 1 attempt
    union select 'Daniel' student, 'Computer Science' subject, 'F' grade, 1 attempt
    union select 'Daniel' student, 'Computer Science' subject, 'A' grade, 2 attempt
) x
inner join @students st on st.name = x.student
inner join @subjects su on su.title = x.subject

--list all students
select * 
from @students st
--where there isn't
where not exists
(
--any subject
    select top 1 1 
    from @subjects su 
--for which that student did not take an exam
    where su.id not in 
    (
        select subjectid 
        from @exams e 
        where studentId = st.id
    )
)

Upvotes: 1

Related Questions