asked on

How to output all records from a subquery that may not have existing join records in the primary query?

I'm searching historical grades to compile the accumulative GPA in a sub-query. I want to bring all these records back and search the same historical grades table but only for this year's semester 1 (S1) grades. and output the accumulative and the S1 GPA's.
My results don't output any student that doesn't have an S1 grade.

SELECT Student_number,Cum_GPA,SUM(sgs.earnedcrhrs) AS Earned,sum(sgs.potentialcrhrs) AS Potential,ROUND((AVG(sgs.gpa_points + sgs.gpa_addedvalue)),4) AS S1_GPA
FROM (SELECT s.id,s.student_number, ROUND((SUM((sg.gpa_points + sg.gpa_addedvalue) * sg.earnedcrhrs) / SUM(sg.potentialcrhrs)),4) AS Cum_GPA
FROM storedgrades sg
RIGHT OUTER JOIN students s on sg.studentid = s.id
WHERE sg.storecode = 'Y1' AND sg.grade_level > 8 AND Ps_Customfields.Getcf('Students',S.Id,'selectgrp3') = '2016 12th grader' AND sg.excludefromgpa = 0
GROUP BY s.id,s.student_number)
LEFT JOIN storedgrades sgs ON id = sgs.studentid
WHERE sgs.termid BETWEEN '2600' AND '2699' AND sgs.storecode = 'S1' AND sgs.excludefromgpa = 0
GROUP BY Student_numberCum_GPA

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Don Scarpetti

ASKER

Hi Guy,

Thanks it works great! It takes awhile longer but I get the right results.

But I have a question for you.

What is the difference in adding the "where" conditions to the JOIN statement rather than keeping them in the WHERE clause?

Thanks again,

Don

SOLUTION