Select Query for Comma separated IDs

veeruu · February 9th, 2006, 12:59 AM

hi,
my sample SQL Server DB Tables are like,
SID Skill
--- -------
1 JAVA
2 ORACLE
3 C
4 C++

PID Skillset
--- ---------
1 1,2,3
2 2,4
3 1,2,3,4
I need the Query to display Person skills as follows...
PID Skillset
--- --------------
1 Java,Oracle,C
2 Oracle,C++
3 Java,Oracle,C,C++

and another query for Search..
if i give the search string as Java or i will pass the SID 1. i need to diplay the person records which contains the SID.

output will be...
PID Skillset
--- --------------
1 Java,Oracle,C
3 Java,Oracle,C,C++

or

PID Skillset
--- ---------
1 1,2,3
3 1,2,3,4

Plz help meee..
Thanking you in advance for your help.

rodmcleay · February 9th, 2006, 01:31 AM

Hi Veeruu,
I would first recommend that you change your data structure a little.
I would recommend that your table skillset be amended to have skill sets a seperate record for each skill in the skillset.
SkillTable
SID Skill
--- -------
1 JAVA
2 ORACLE
3 C
4 C++

PersonSkillTable
SKID PID SID
---- --- ---
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3

So you add a new row to this table for each skill instead of having a string holding the skill sets.

From here the queries are easy:

Code:

select PID, SID from PersonSkillTable order by PID

and

Code:

select ps.PID, s.Skill
from PersonSkillTable ps, SkillTable s 
where ps.SID = s.SID
order by ps.PID

These will not give you a comma seperated list like you have shown but a recordset full of rows each one containing hte person id and one skill, this will be the better format for whatever user interface you will be using to display the data.

PS.Code Not tested for syntax, ask back if problem occurs

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

veeruu · February 9th, 2006, 02:55 AM

hi rodmcleay..
what you said is right, But i need to dispaly all Persons with their total skillset in the following format.This is my requirement.

PID Skillset
--- --------------
1 Java,Oracle,C
2 Oracle,C++
3 Java,Oracle,C,C++

if i use the table which is given by you ..how can i format the output as My required one (AS Above). Using Select Statement

Thanks for your quick response.
veeruu

rodmcleay · February 9th, 2006, 03:17 AM

I cant see how that can be done without manipulating strings once the data has been retreived.

Can anyone else help with this.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================