I have 4 tables built with JOINS and I would like to SELECT DISTINCT rows on the setsTbl.s_id so they always show regardless if there's relational data against them or not!. This is what I have at present which displays the data but doesn't display all of but not the entire distinct row!

 SELECT setsTbl.s_id, setsTbl.setName, userProfilesTbl.no + ' ' +
 userProfilesTbl.surname AS Name,  trainingTbl.t_date,
 userAssessmentTbl.o_id FROM userProfilesTbl LEFT OUTER JOIN
 userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId
 FULL OUTER JOIN trainingTbl ON userAssessmentTbl.tt_id =
 trainingTbl.tt_id RIGHT OUTER JOIN setsTbl ON trainingTbl.s_id =
 setsTbl.s_id WHERE (userProfilesTbl.st_id=@st_id AND
 userProfilesTbl.sh_id=@sh_id) AND (DATEPART(yyyy,t_date) = @y_date )
 OR (userAssessmentTbl.o_id IS NULL) ORDER BY setName ASC, t_date ASC

With this statement I get some of the rows (the ones with data against them) but as stated the s_id field does not return distinct.

This following inner select statement works in part when used in SQL Query analyzer and returns pretty much the data i require

s_id   setName  Name       o_id      
-----  -----   -----      ------
1       100     Barnes      2
2       100     Beardsley   3
3       101     Aldridge    1
4       102     Molby       2
5       102     Whelan      3

but not when used outside of that environment.

select * 
    from 
    (
    SELECT userProfilesTbl.serviceNo + ' ' + userProfilesTbl.surname AS Name, userProfilesTbl.st_id, userProfilesTbl.sh_id, userAssessmentTbl.o_id, setsTbl.s_id, setsTbl.setName,  row_number() over 
            (
                partition by setsTbl.s_id
                order by setsTbl.s_id
            ) r
    FROM userProfilesTbl LEFT OUTER JOIN
                      userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId FULL OUTER JOIN
                      trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id RIGHT OUTER JOIN
                      setsTbl ON trainingTbl.s_id = setsTbl.s_id
    ) x
    where x.r = 1

Not receiving any errors just not displaying the data?