Can somebody check my JOINs please?

[Zark3]

Hello all,

I've come to you guys for some help, because this query is getting too
complicated for me. Apparently I don't sufficiently grasp the concept
of outer joins etc.

My database (not designed by me, so I cannot change, only query)
consists of these tables (snippet):
--
Course (no ext. refs)
Coursegroup (ext. ref to Course)
CoursegroupUser (ext. refs to Coursegroup & User)
Navigation (ext. ref to Course)
NavigationSeries (ext. refs to Navigation & Series)
Series (no ext. refs)
SeriesScore (ext. refs to Series, User, Course & Navigation)
User (no ext. refs)
--

The query I want to write is one that retrieves the latest attempt
(highest SeriesScore.AttemptNo) for every user in a certain
coursegroup for all series in a certain course.
Since it cannot be guaranteed that everyone completes every series,
there will be null values and my problem is in retrieving these non-
existing values.

I can retrieve all series that are linked to a course like so:
--
SELECT DISTINCT Series.ID, Series.Title
FROM Series
INNER JOIN SeriesScore ON Series.ID = SeriesScore.fkSeries
WHERE SeriesScore.fkCourse = @CourseId
--

I can retrieve all users that are contained in a coursegroup like so:
--
SELECT ID, Firstname, Lastname
FROM User
WHERE ID IN (
SELECT fkUser FROM CoursegroupUser WHERE fkCoursegroup =
@CoursegroupId
)
--

However, when I want to combine these queries I only get back the
'existing' data, not indicating who -didn't- make a certain series.
i.e. if user #1 made series #1 and #2 and user #2 only made series #1,
i want to see:
--
User Series Score
1 1 x
1 2 x
2 1 x
2 2 [NULL] <-- explicitly displayed non-existing value
--

My attempts so far have halted at:
--
SELECT SeriesScore.ID, Series.ID AS fkSeries, SeriesScore.Score,
SeriesScore.AttemptNo, Series.Cesuur
FROM SeriesScore
RIGHT OUTER JOIN Series ON SeriesScore.fkSeries = Series.ID
RIGHT OUTER JOIN Course ON SeriesScore.fkCourse = Course.ID
RIGHT OUTER JOIN NavigationSeries ON Series.ID =
NavigationSeries.fkSeries
RIGHT OUTER JOIN Navigation ON NavigationSeries.fkNavigation =
Navigation.ID
RIGHT OUTER JOIN Coursegroup ON SeriesScore.fkCourse =
Coursegroup.fkCourse
WHERE Coursegroup.ID = @CoursegroupId
AND fkUser = @UserId
--

The problem with this query is that it does not show nulls for the
missing values (just omits) and that it shows all attempts instead of
the one with the highest SeriesScore.AttemptNo.
I feel like I'm really close and that I'm just using the wrong JOIN
somewhere, but who can tell me where?


Thanks in advance,

Chris