How to exclude rows from a search?

[ryodoan]

[Resolved] How to exclude rows from a search?

Ok, I have one table of students, and I have another table of students that have already submitted a questionare. I want to find the students that have not submitted the questionaire.

Example:

Table1: Students
pkStudentID - int
vcName
...ect...

Table2: QuestionaireResults
pkResultID - int
fkStudentID - int
ect...

How do I write a stored procedure, or maybe code in asp.net, that will give me a list of students that have NOT submitted the questionaire?

 

[Tarik]

Ok i do not have MS SQL Server in front of me now to try this code but try it and the null values will indicate the student who did not atend the Questionaire

Select S.pkStudentID , Q.pkResultID
From Students S  FULL OUTER JOIN QuestionaireResults Q
On S.pkStudentID = Q.fkStudentID

This is SQL statement you can pass it to SQLCommand instead of using SP and in case you would like to use SP Just Put The Code Like This

Create Procedure AbsentQuestionareStd
as
Select S.pkStudentID , Q.pkResultID
 From Students S  FULL OUTER JOIN QuestionaireResults Q
 On S.pkStudentID = Q.fkStudentID
Go

 

[ryodoan]

That is almost exactly what I needed. Thank you so much.

I was using some pretty crazy table manipulation inside VB.NET, and I knew there had to be some way to make a stored procedure that did it.

Ok, for anyone else that has/had this problem, here is how the code looks in a view:

SELECT dbo.Questionaire.fkStudentID, dbo.Students.vcname, dbo.Students.pkStudentID
FROM dbo.Students FULL OUTER JOIN
dbo.Questionaire ON dbo.Students.pkStudentID = dbo.Questionaire.fkStudentID
WHERE (dbo.Questionaire.fkStudentID IS NULL)

If only I had know this last night, it would of saved me at least 2 hours of coding, lol....