CODEWITHSUNDEEP
sqljoin
olefrank
olefrank

Reputation: 6810

SQL: tricky join

I'm building software for exam signup and grading:

I need to get data from these two tables:

Exams

|------------------------------------------------------------|           
| ExamId  |  ExamTitle  |  EducationId  |  ExamDate  |               
|------------------------------------------------------------|

ExamAttempts

|-----------------------------------------------------------------------------|
| ExamAttemptId  |  ExamId  |  StudentId  |  Grade  |  NotPresentCode  |        
|-----------------------------------------------------------------------------|
  1. Students attends an education
  2. Educations have multiple exams
  3. Students have up to 6 attempts per exam
  4. Every attempt is graded or marked as not present

Students can sign up for an exam if: - not passed yet (grade below 2) - has not used all attempts

I want to list every exams that a student can sign up for.

It maybe fairly simple, but I just can't get my head around it and now I'm stuck! I've tried EVERYTHING but haven't got it right yet. This is one of the more hopeless tries I made (!):

CREATE PROCEDURE getExamsOpenForSignUp
@EducationId int,
@StudentId int
AS
SELECT ex.*
FROM Exams ex
LEFT JOIN (
    SELECT ExamId, COUNT(ExamId) AS NumAttempts
    FROM ExamAttempts
    WHERE StudentId = @StudentId AND grade < 2 OR grade IS NULL
    GROUP BY ExamId
) exGrouped ON ex.ExamId = exGrouped.ExamId
WHERE educationid = @EducationId and exGrouped.ExamId IS NULL OR exGrouped.NumAttempts < 6;
GO

What am i doing wrong? Please help...

Upvotes: 2

Views: 651

Answers (3)

YK1
YK1

Reputation: 7612

Check if this works in your SP:

Select EduExams.ExamId
from
    (select * from Exams 
     where Exams.EducationId = @EducationId) EduExams 
left outer join 
    (select * from ExamAttempts 
     where ExamAttempts.StudentId = @StudentId) StudentAttempts
on EduExams.ExamID = StudentAttempts.ExamId
group by EduExams.ExamId
having count(StudentAttempts.ExamAttemptId) < 6 
       and ((max(StudentAttempts.Grade) is null) or (max(StudentAttempts.Grade) < 2))

Upvotes: 1

olefrank
olefrank

Reputation: 6810

OK thank you both for your help - much appreciated!

Based on @Gordon Linoffs solution. this is what I ended up with:

SELECT driver.ExamId, driver.ExamTitle
FROM (
    SELECT @StudentId AS StudentId, e.ExamId, e.ExamTitle
    FROM exams e
    WHERE e.EducationId = @EducationId
) driver 
    LEFT JOIN (
        SELECT ea.ExamId, ea.StudentId
        FROM ExamAttempts ea
        WHERE ea.studentId = @StudentId
        GROUP BY ea.ExamId, ea.StudentId
        HAVING MAX(grade) >= 2 OR COUNT(*) >= 6
    ) NotEligible
        ON driver.ExamId = NotEligible.ExamId AND driver.StudentId = NotEligible.StudentId
WHERE NotEligible.ExamId IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You need to start with a list of all possibilities of exams and students and then weed out the ones that don't meet the requirements.

select driver.StudentId, driver.ExamId
from (select @StudentId as StudentId, e.ExamId
      from exams e
      where e.EducationId = @EducationId
     ) driver left outer join
     (select ea.ExamId, ea.StudentId
      from ExamAttempts ea
      group by ea.ExamId, ea.StudentId
      having max(grade) >= 2 or -- passed
             count(*) >= 6
     ) NotEligible
     on driver.ExamId = NotEligible.ExamId and
        driver.StudentId = NotEligible.StudentId
where NotEligible.ExamId is NULL

The structure of this query is quite specific. The driver table contains all possible combinations. In this case, you have only one student and all exams are in the "education". Then the left join determines which are not eligible, based on your two requirements. The final where is selecting the non-matches to the not-eligible -- or the exams that are eligible.

Upvotes: 1

Related Questions