get Split names from id of other table

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

General SQL Server Forums

New to SQL Server Programming

get Split names from id of other table

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-07-10 : 05:53:31

Table :: master_Staff

StaffID - Number
StaffName - String

Table :: master_Class

ClassID - Number
ClassTeacher - Number ( Forign Key of StaffID )

I have a set of StaffID saved using "," in the table class -------- 10,20,30

Now i want a Query to get the staff name -- hari, gopal, sekar , by using the classID

ie::
select staffname from master_staff where staffid in ( select classteacher from master_class where classID = 1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-10 : 06:01:02

Read about Normalisation http://www.datamodel.org/NormalizationRules.html
And Split function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-07-10 : 06:08:58

i can't understand..
can i get the exact query or my method is wrong ..

do help me

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 06:21:32

shanmugaraj - To help us help you, please post some sample data for the structure you've given us, and the result you would like for that sample data (for a given input).

It's much easier to work with examples...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-10 : 06:24:58

quote:
Originally posted by shanmugaraj

Table :: master_Staff

StaffID - Number
StaffName - String

Table :: master_Class

ClassID - Number
ClassTeacher - Number ( Forign Key of StaffID )

I have a set of StaffID saved using "," in the table class -------- 10,20,30

Now i want a Query to get the staff name -- hari, gopal, sekar , by using the classID

ie::
select staffname from master_staff where staffid in ( select classteacher from master_class where classID = 1)

How can you stored comma separated string of IDs in an INT column? And if it is a foreign key, how will it allow you to store string of IDs like "1,2,3" ?

Please post some sample data from the table.

Harsh Athalye
India.
"Nothing is Impossible"

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-07-10 : 06:29:06

sorry , ClassTeacher is Text Filed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-10 : 06:49:28

quote:
Originally posted by shanmugaraj

sorry , ClassTeacher is Text Filed

Read about Normalisation. Dont store csv values in a column that will make the things complecated than expected. Read about Normalisation

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-07-10 : 06:53:28

Thanks mr.Mathivanan.

i will try

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 06:53:43

Yes. Read about normalization and normalize your structure. You queries will be far more efficient, and easier to write and maintain.

Here's the hack to work with your existing (and bad) structure:

--data
declare @master_Staff Table (StaffID int, StaffName varchar(20))
insert @master_Staff
          select 10, 'hari'
union all select 20, 'gopal'
union all select 30, 'sekar'
union all select 40, 'smith'

declare @master_Class Table (ClassID int, ClassTeacher varchar(20))
insert @master_Class
          select 1, '10,20,30'
union all select 2, '10,40'

--input
declare @ClassID int
set @ClassID = 1

--calculation (with output in 2 different formats)
declare @ClassTeacher varchar(20)
select @ClassTeacher = ClassTeacher from @master_Class where ClassID = @ClassID

select s.* from @master_Staff s
where ',' + @ClassTeacher + ',' like '%,' + cast(StaffID as varchar(5)) + ',%'

declare @StaffName varchar(100)
select @StaffName = isnull(@StaffName + ', ', '') + StaffName from @master_Staff s
where ',' + @ClassTeacher + ',' like '%,' + cast(StaffID as varchar(5)) + ',%'
select @StaffName

/*results
StaffID     StaffName            
----------- -------------------- 
10          hari
20          gopal
30          sekar

--------------------------------
hari, gopal, sekar
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 06:59:17

And here's an example with a better table structure:

--data
declare @master_Staff Table (StaffID int, StaffName varchar(20))
insert @master_Staff
          select 10, 'hari'
union all select 20, 'gopal'
union all select 30, 'sekar'
union all select 40, 'smith'

declare @master_ClassTeacher Table (ClassID int, ClassTeacher int)
insert @master_ClassTeacher
          select 1, 10
union all select 1, 20
union all select 1, 30
union all select 2, 10
union all select 2, 40

--input
declare @ClassID int
set @ClassID = 1

--calculation (with output in 2 different formats)
select s.* from @master_Staff s inner join @master_ClassTeacher t on s.StaffID = t.ClassTeacher
where ClassID = @ClassID

declare @StaffName varchar(100)
select @StaffName = isnull(@StaffName + ', ', '') + StaffName
from @master_Staff s inner join @master_ClassTeacher t on s.StaffID = t.ClassTeacher
where ClassID = @ClassID
select @StaffName

/*results
StaffID     StaffName            
----------- -------------------- 
10          hari
20          gopal
30          sekar

--------------------------------
hari, gopal, sekar
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources