Trouble joining two tables properly.

[kts]

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.

 

[kickstart]

Hi

 

Do you have a 3rd table of students?

 

If so something like

 

SELECT *
FROM Students a
CROSS JOIN OTRegents b
LEFT OUTER JOIN FullCredits c
ON a.StudentID = c.StudentID
AND b.Title = c.Title

 

All the best

 

Keith

[kts]

The table of students has already been consolidated... when I try to LEFT JOIN  ON c.Title = oc.Title I'm not getting all 12 of the rows for each student like I should be...

[kickstart]

Hi

 

That is because you are doing a join on a table that only has a row when s student  has a mark.

 

If you cross join the list of students with the list of courses then you will get a row for every combination of student and course, then left join that with the results to populate the results for that student / course if there is a result (and if no the result will be null).

 

Not sure why you have consolidated the students table into one just giving their results.

 

If you haven't got a list of students then you will have to create one in the SQL (it would be better to redesign the database to have a list of students), something like this

 

SELECT *
FROM (SELECT DISTINCT StudentId FROM FullCredits) a
CROSS JOIN OTRegents b
LEFT OUTER JOIN FullCredits c
ON a.StudentID = c.StudentID
AND b.Title = c.Title 

 

All the best

 

Keith