a stored procedure question?

[mshen2004]

I have two tables.the structures like this

table one (total student table)

id student_id
1 10
2 11
3 12
4 13
5 14

table two (graduated student table)

id student_id
1 10
2 12


I want to get all of the student id who have not graduated.
that is the records in table one but not in table two.the
result like this.

id student_id
2 11
4 13
5 14

I want to write a stored procedure to do it.please give me a idea how to write the stored procedure.

Thanks a lot

[CardboardHammer]

The "id" field in both tables is redundant, presuming that a particular "student_id" may only appear once per table. If that presumption isn't true, why isn't it? If it is true, get rid of it and use "student_id" as the primary key in both tables. You may find it easier to just put a graduation date/term field in the total table and leave it NULL for students who have yet to graduate, and then get rid of the graduated table.

-----------------------------------------------------------

CREATE PROCEDURE GET_UNGRADUATED_STUDENT_IDS AS
SELECT t.student_id
FROM total t LEFT JOIN graduated g
ON t.student_id = g.student_id
WHERE g.student_id IS NULL

[mshen2004]

similar question like this.

table one (total student table)

id student_id
1 10
2 11
3 12
4 13
5 14

table two (course/student table)

id course_id student_id
1 1 10
2 1 12
3 2 10
4 2 11

(one student maybe take many courses)

I need to get the student who do not take
course two(course_id<>2).

Please give me a idea how to write the stored procedure.

Thanks a lot.

[CardboardHammer]

The "id" field can be removed from the course/student (a.k.a. "enrollment") table, and a composite primary key of student_id and course_id can be used.

Be careful what you're asking for, as you can end up with a great deal of inefficiency as the number of students grows over time.

-----------------------------------------------------------

CREATE PROCEDURE GET_STUDENT_IDS_NOT_IN_COURSE
@course_id AS int --an input parameter
AS
SELECT student_id
FROM total
WHERE student_id NOT IN
(SELECT student_id FROM enrollment WHERE course_id = @course_id)

-----------------------------------------------------------

--EXAMPLE OF CALLING SP FROM T-SQL
EXEC GET_STUDENT_IDS_NOT_IN_COURSE 2