a stored procedure question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

a stored procedure question

Author

Topic

mshen
Starting Member

8 Posts

Posted - 2004-08-16 : 23:42:36

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.

Thnaks a lot

mshen
Starting Member

8 Posts

Posted - 2004-08-16 : 23:43:34

thanks for your help

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-16 : 23:53:47

[code]
create procedure dbo.proc_StudtentsNotYetGraduated
as
select t1.id, t1.student_id
from table1 t1
where not exists
(
select *
from table2 t2
where t2.student_id = t1.student_id
)
go[/code]

- RoLY roLLs

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-17 : 03:59:16

create procedure dbo.spStudentsNotGraduated
as
set nocount on

select t1.id, t1.student_id
from table1 t1 left join table2 t2 on t1.student_id = t2.student_id
where t2.id is null

-- What is the purpose of the id columns ?
-- /rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources