Help in SP - 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

SQL Server Development (2000)

Help in SP

Author

Topic

kingkong
Starting Member

16 Posts

Posted - 2004-08-25 : 23:59:13

Hi:
I am not good in SQL. Hope you guys can guide me writing a Stored Procedure to genrate a New Table list as below

Table A
student, Teacher, Staff
Sam, Peter, Tim
Frank, Mary, Sue

Table B
Role, Name, ID
Student, Sam, 1
Student, Frank, 2
Teacher, Peter, 3
Teacher, Mary, 4
Staff, Tim, 5
Staff, Sue, 6

New Table
Student, Teacher, Staff
1, 3, 5
2, 4, 6

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-26 : 00:08:02

Where are you a student at? Give it a shot first and post the result here. We'll help you out from there.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kingkong
Starting Member

16 Posts

Posted - 2004-08-26 : 17:50:02

No, I am not a student!

I have a project need to write a SP to Update Table A (not create a New table that I list as above). It is only the sample idea, but closed to what I need for my my real project.

Since I am a CBT (Computer Based Training) developer that use products from Macromedia daily, but not expertise for using SQL/SP. As the data as below:

Table A
student, Teacher, Staff
Sam, Peter, Tim
Frank, Mary, Mary

Table B
Role, Name, ID
Student, Sam, 1
Student, Frank, 2
Teacher, Peter, 3
Teacher, Mary, 4
Staff, Mary, 4
Staff, Tim, 5

Table A
Student, Teacher, Staff
1, 3, 5
2, 4, 4

Update Table A
Set student = table B.ID
where student = table b.name

Update Table A
Set Teacher = table B.ID
where Teacher = table b.name

Update Table A
Set Staff = table B.ID
where Staff = table b.name

But it doesn't seems to work, can I write all in one Query calling from SP instead! Or do I need using the VIEW function...

Many thanks if someone can guide me through.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-26 : 19:29:12

[code]update TableA
SET student = s.[ID], Teacher = t.[ID], Staff = sf.[ID]
FROM TableA A INNER JOIN tableb s ON A.Student = s.[Name] AND s.Role = 'Student'
INNER JOIN tableb t ON A.Teacher = t.[Name] AND t.Role = 'Teacher'
INNER JOIN tableb sf ON A.Staff = sf.[Name] AND sf.Role = 'Staff'[/code]

kingkong
Starting Member

16 Posts

Posted - 2004-08-26 : 22:34:22

Thanks Timmy:
I will try this query concepts when I back to office tomorrow.

Many Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources