Hi

Does this help?

SELECT *

FROM

(

SELECT

P.ProfID

,P.ProfName

,CASE WHEN RowNum1.CourseID IS NOT NULL THEN RowNum1.CourseID ELSE RowNum2.CourseID END AS CourseID

,CASE WHEN RowNum2.LanguageID IS NOT NULL THEN RowNum2.LanguageID ELSE RowNum1.LanguageID END AS LanguageID

,RowNum1.RN1

,RowNum2.RN2

FROM

Professor P

LEFT JOIN(SELECT

DeptID AS DeptID

, LanguageID AS LanguageID

, CourseID AS CourseID

, ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY CourseID DESC ) RN1

FROM

Class

) AS RowNum1

ON P.DeptID = RowNum1.DeptID

LEFT JOIN(SELECT

DeptID AS DeptID

, LanguageID AS LanguageID

, CourseID AS CourseID

, ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY LanguageID DESC ) RN2

FROM

Class

) AS RowNum2

ON P.DeptID = RowNum2.DeptID

) Tbl

WHERE

Tbl.RN1 = 1

AND Tbl.RN2 = 1

Andy