SELECT Statement without duplicate rows on the multiple join tables
- by theBo
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?
