SQL Server Performance Forum – Threads Archive

Select Query for Comma separated IDs

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 a SELECT 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.

---

this is not a good DB Design. YOu should have table like this
PID Skillset
— ———
1 1
1 2
1 3
2 2
2 4
3 1
3 2
3 3
3 4 this will make not only a simple query but also increase the performances. —————————————-
http://spaces.msn.com/members/dineshasanka

---

As said, properly normalise the table
http://www.datamodel.org/NormalizationRules.html Madhivanan Failing to plan is Planning to fail

---

To explain a bit further: Your Skillset table violates First Normal Form (1NF). 1NF requires among other all columns to contain only atomic values (not going into a discussion here, what that might mean). You’re storing a list of skills that might contain more than one value, thus might be multivalued. Sticking to your approch begs for massive trouble in the future. By any chance, properly normalize as Dinesh suggested. To retrieve the data in the way you want, you can then use something like this mentioned here:http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

]]>