Trouble Combining Two Tables with Comparisons

[kts]

Hi,

 

In simple, I have two tables.

Table One: CreditDetails

holds a students information, including mark, department, etc)

 

Second table: OnTrackReq

holds the requirements the student must meet. (ontrack, grade, type, taken will be filled to 1 if its required 0 if not)

 

So ultimately, I am looping through each student and comparing their mark (based on department and grade) to the ontrackreq tables which also has grade, ontrack(the number they need to meet) and o.taken i would consider to be passed.

 

o.taken works in the sense of it there is a 1 it is passed.

 

What I forsee the problem is, it will only work if I have the department, grade and type in my where outside the case. Where I need each row to just show me taken if the Department = 'English' and Grade = '11' in OnTrackReq has a 1.

 

Hopefully I explained this properly.. I am just trying to make sure this is possible.

 

SELECT c.StudentID StudentID,c.LastName, c.FirstName, c.Grade Grade, c.Title, c.Department, c.Mark,
(case when c.Grade = o.Grade AND c.Mark > o.Ontrack AND c.Department = o.Department AND c.Type = o.Type then o.taken end) as Passed

FROM CreditDetails c JOIN OnTrackReq o ON (c.Grade = o.Grade AND c.Type = o.Type) WHERE c.Type = 'Regents' AND c.Grade = '12' GROUP BY c.StudentID, c.Department, c.Type ORDER BY c.LastName ASC  

 

Thanks,

K

[fenway]

Sorry, I don't follow.