I am using below query to pull the students who failed . along with showing the marks of Maths, science and social . In same table we have all the data stored by QueryID and Response columns.
SELECT v.Number ,v.[Name],q.Response as Alert
,q2.Response as 'Math Marks'
,q3.Response as 'Science Marks'
,q4.Response as 'Social Marks'
** FROM StuTable v JOIN StudentQueries q ON v.ID=q.ID**
** Inner join StudentQueries q2 ON (q2.ID = q.ID and q2.QueryID = 'Maths')**
** Inner join StudentQueries q3 ON (q3.ID = q.ID and q3.QueryID = 'Science')**
** Inner join StudentQueries q4 ON (q6.ID = q.ID and q4.QueryID = 'Social')**
q.QueryID IN('Fail') AND
q.[DateTime] BETWEEN @BeginDate and @EndDate
order by q.DateTime desc
But problem with above query is even though student is failed if he don't have marks entered for Maths then he is not showing up. But I want all the students who failed list along with their marks if it is entered, if not that need to be blank.
Can you please help me on this ?