How to exclude rows from a search?

ryodoan

Well-known member
Joined
Jul 16, 2004
Messages
65
Location
Ohio
Programming Experience
3-5
[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?
 
Last edited:

Tarik

Active member
Joined
Apr 16, 2005
Messages
41
Location
Egypt-Alexandria
Programming Experience
3-5
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
VB.NET:
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
VB.NET:
Create Procedure AbsentQuestionareStd
as
Select S.pkStudentID , Q.pkResultID
 From Students S  FULL OUTER JOIN QuestionaireResults Q
 On S.pkStudentID = Q.fkStudentID
Go
 

ryodoan

Well-known member
Joined
Jul 16, 2004
Messages
65
Location
Ohio
Programming Experience
3-5
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....
 
Top Bottom