Self-join to return either/or results?
-
Hi, given the following tables (a student assessment system): Student: StudentID StuAssess: StudentID, SubjectCode, AssessID, CritID, CritVal Each student has one row in StuAssess for each subject for each assessment or target. The round/target is identified by the AssessID and CritID. Is it possible to write a query that will return each row in StuAssess for a student where there is either a 'Result' (i.e. AssessID = 10, CritID = 35) AND/OR a 'Target' (i.e. AssessID = 11, CritID = 42), something like this as a resultset: SubjectCode, ResultCritVal, TargetCritVal so a null 'ResultCritVal' but populated 'TargetCritVal' would indicate a target without a result, null 'Target' but populated 'Result' means result without a target, and null for both columns means no result or target. I've resorted to using a 2-pass query and handling the two resultsets in my code, but if it's possible, I'd like to just have the one resultset. Hope I've managed to explain the requirement.
-
Hi, given the following tables (a student assessment system): Student: StudentID StuAssess: StudentID, SubjectCode, AssessID, CritID, CritVal Each student has one row in StuAssess for each subject for each assessment or target. The round/target is identified by the AssessID and CritID. Is it possible to write a query that will return each row in StuAssess for a student where there is either a 'Result' (i.e. AssessID = 10, CritID = 35) AND/OR a 'Target' (i.e. AssessID = 11, CritID = 42), something like this as a resultset: SubjectCode, ResultCritVal, TargetCritVal so a null 'ResultCritVal' but populated 'TargetCritVal' would indicate a target without a result, null 'Target' but populated 'Result' means result without a target, and null for both columns means no result or target. I've resorted to using a 2-pass query and handling the two resultsets in my code, but if it's possible, I'd like to just have the one resultset. Hope I've managed to explain the requirement.
Maybe the "Case" statement will work for you. http://msdn.microsoft.com/en-us/library/ms181765.aspx[^]
-
Maybe the "Case" statement will work for you. http://msdn.microsoft.com/en-us/library/ms181765.aspx[^]
Thanks David. Maybe it's getting too late here, but I can't figure out how CASE would help me, given that for any given subjectcode, each student would have EITHER a result (AssessID = 10, CritID = 35) OR a target (AssessID = 11, CritID = 42) OR both OR neither. I've got it in my head that I can only accomplish this through one or more self-joins, but the only common columns I can work with are StudentID and SubjectCode - there's no concept of a left or right table here. Or am I really suffering brain-fade (it's been a long day) :-) Derek
-
Hi, given the following tables (a student assessment system): Student: StudentID StuAssess: StudentID, SubjectCode, AssessID, CritID, CritVal Each student has one row in StuAssess for each subject for each assessment or target. The round/target is identified by the AssessID and CritID. Is it possible to write a query that will return each row in StuAssess for a student where there is either a 'Result' (i.e. AssessID = 10, CritID = 35) AND/OR a 'Target' (i.e. AssessID = 11, CritID = 42), something like this as a resultset: SubjectCode, ResultCritVal, TargetCritVal so a null 'ResultCritVal' but populated 'TargetCritVal' would indicate a target without a result, null 'Target' but populated 'Result' means result without a target, and null for both columns means no result or target. I've resorted to using a 2-pass query and handling the two resultsets in my code, but if it's possible, I'd like to just have the one resultset. Hope I've managed to explain the requirement.
SELECT DISTINCT
A.StudentID,
A.SubjectCode,
B.CritVal As ResultCritVal,
C.CritVal As TargetCritVal
FROM StuAssess A LEFT JOIN StuAssess B
ON A.StudentID=B.StudentID, A.SubjectCode=B.SubjectCode
LEFT JOIN StuAssess C
ON A.StudentID=C.StudentID, A.SubjectCode=C.SubjectCode
WHERE (B.AssessID=10 AND B.CritID=35) AND (C.AssessID=11 AND C.CritID=42)I haven't test the code but I did something similar to that sometime back. BTW... StuAssess ... I'd change that name.
-
Hi, given the following tables (a student assessment system): Student: StudentID StuAssess: StudentID, SubjectCode, AssessID, CritID, CritVal Each student has one row in StuAssess for each subject for each assessment or target. The round/target is identified by the AssessID and CritID. Is it possible to write a query that will return each row in StuAssess for a student where there is either a 'Result' (i.e. AssessID = 10, CritID = 35) AND/OR a 'Target' (i.e. AssessID = 11, CritID = 42), something like this as a resultset: SubjectCode, ResultCritVal, TargetCritVal so a null 'ResultCritVal' but populated 'TargetCritVal' would indicate a target without a result, null 'Target' but populated 'Result' means result without a target, and null for both columns means no result or target. I've resorted to using a 2-pass query and handling the two resultsets in my code, but if it's possible, I'd like to just have the one resultset. Hope I've managed to explain the requirement.
How about a view that implements one criteria, called Result_view
create view result_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 10 and CritID = 35
;Then another view that implements the other criteria, called Target_view
create view target_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 11 and CritID = 42
;Then you query the views
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from result_view
union
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from target_view
;:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
How about a view that implements one criteria, called Result_view
create view result_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 10 and CritID = 35
;Then another view that implements the other criteria, called Target_view
create view target_view as
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from StuAssess
where assessId = 11 and CritID = 42
;Then you query the views
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from result_view
union
select StudentID
,SubjectCode
,AssessID
,CritID
,CritVal
from target_view
;:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
I think he is looking for something in the form: StudentID, SubjectCode, ResultCritVal, TargetCritVal
Very true, but he's failed to indicate how a ResultCritVal or TargetCritVal are calculated or populated. He's only shown what values for the original columns are to be. By using the views I suggested, the conditions for each criteria are provided. How those column values are 'translated' to a ResultCritVal or a TargetCritVal would still be necessary. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]