We help IT Professionals succeed at work.

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

211 Views
Last Modified: 2018-02-02
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
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Don ScarpettiAssistant Mgr Data Management

Author

Commented:
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
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Don ScarpettiAssistant Mgr Data Management

Author

Commented:
That clears it up for me.

Thank you Guy
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.