Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

This query gets several AssignmentId's

SELECT AS2.AssignmentId
FROM dbo.AssignmentSummary AS AS2
WHERE AS2.SixweekPosition = 1 AND AS2.TeacherId = 'mggarcia'

This query gets a value for only one assignment through the variable @assignmentId

SELECT S.StudentId, 
        CASE WHEN OW.OverwrittenScore IS NOT NULL
            THEN OW.OverwrittenScore
            ELSE dbo.GetFinalScore(S.StudentId, @assignmentId)
        END AS FinalScore
FROM dbo.Students AS S
LEFT JOIN dbo.OverwrittenScores AS OW
    ON S.StudentId = OW.StudentID
    AND OW.AssignmentId = @assignmentId
WHERE S.ClassId IN (
    SELECT C.ClassId
    FROM Classes AS C
    WHERE C.TeacherId = @teacherId
)

As I pointed, in the last query works when you assign a value through the variable and returns a table. Now I want to get a table of several AssignmentId's from the first query.

What do I need? A Join table? I have no idea about what to do now.

share|improve this question
    
What does dbo.GetFinalScore() do? –  Dibstar May 17 '13 at 7:48

3 Answers 3

up vote 2 down vote accepted
AND OW.AssignmentId IN
(
    SELECT AS2.AssignmentId
    FROM   dbo.AssignmentSummary AS AS2
    WHERE  AS2.SixweekPosition = 1 AND AS2.TeacherId = 'mggarcia'
)

the suggestion can be optimize if you can tell me how are the tables are related with each other.

share|improve this answer
    
What do you need that I provide you? –  Darf Zon May 17 '13 at 1:49
    
a simple explanation on how the tables are related with each other. on what column are they linked? etc.. –  John Woo May 17 '13 at 1:56

You can combine them using in:

SELECT S.StudentId, 
        CASE WHEN OW.OverwrittenScore IS NOT NULL
            THEN OW.OverwrittenScore
            ELSE dbo.GetFinalScore(S.StudentId, @assignmentId)
        END AS FinalScore
FROM dbo.Students AS S
LEFT JOIN dbo.OverwrittenScores AS OW
    ON S.StudentId = OW.StudentID
    AND OW.AssignmentId in (SELECT AS2.AssignmentId
                            FROM dbo.AssignmentSummary AS AS2
                            WHERE AS2.SixweekPosition = 1 AND AS2.TeacherId = 'mggarcia'
                           )
WHERE S.ClassId IN (
    SELECT C.ClassId
    FROM Classes AS C
    WHERE C.TeacherId = @teacherId
)

There may be ways to simplify this query. This does a direct conversion of substituting the first query into the second.

share|improve this answer

Use APPLY operator with correlated subquery. Also you can replace CASE expression to function ISNULL.

SELECT S.StudentId, 
       ISNULL(o.OverwrittenScore, dbo.GetFinalScore(S.StudentId, o.AssignmentId)) AS FinalScore
FROM dbo.Students AS S
  OUTER APPLY (
               SELECT OW.OverwrittenScore, AS2.AssignmentId
               FROM dbo.OverwrittenScores AS OW JOIN dbo.AssignmentSummary AS AS2
                 ON OW.AssignmentId = AS2.AssignmentId
               WHERE AS2.SixweekPosition = 1 AND AS2.TeacherId = 'mggarcia'
                 AND S.StudentId = OW.StudentID
               ) o
WHERE S.ClassId IN (
                    SELECT C.ClassId
                    FROM Classes AS C
                    WHERE C.TeacherId = @teacherId
                    )
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.