I have two tables.
OTRegents just has 12 rows one for each "Title"
FullCredits has all of the results by studentid for any completed Title's with Mark. I am trying to display All Title's regardless if they exist in FullCredits and have them show up null if they do not exist,but still have them display so that each StudentID has 12 rows regardless.
When I use this query:
SELECT c.StudentID,
oc.Department,
oc.Title,
(case when oc.Title = c.Title then c.Mark else 0 end) Mark,
(case when c.Grade >= oc.Grade then 1 else 0 end) Required,
(case when c.Grade >= oc.Grade AND c.Mark >= oc.OnTrack then 1 else 0 end) Passed
FROM FullCredits c JOIN OTRegents oc ON (oc.Title = c.Title)
WHERE c.Type = 'Regents' GROUP BY c.StudentID, oc.Department, oc.Title
I am receiving the proper mark's for each test, but the problem is not all of the tests are showing. I need it show all 12 tests for each StudentID
When i Remove the
FROM FullCredits c JOIN OTRegents oc ON ---> remove the join on (oc.Title = c.Title)
I get all of the tests, but the mark will only show correctly for ONE test.
I am simply trying to have all 12 rows in the OTRegents be shown for each StudentID and show Mark for each one, if there is no row existing for the Title/Mark for that StudentID I need it to still show up, but for it to be null.
Hopefully this makes sense, its clearly a joining issue, but I have been trying many ways and running out of ideas.