Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 9th, 2006, 12:59 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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 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.



 
Old February 9th, 2006, 01:31 AM
Friend of Wrox
Points: 2,376, Level: 20
Points: 2,376, Level: 20 Points: 2,376, Level: 20 Points: 2,376, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , Australia.
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
======================================
 
Old February 9th, 2006, 02:55 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old February 9th, 2006, 03:17 AM
Friend of Wrox
Points: 2,376, Level: 20
Points: 2,376, Level: 20 Points: 2,376, Level: 20 Points: 2,376, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , Australia.
Posts: 596
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
======================================




Similar Threads
Thread Thread Starter Forum Replies Last Post
Comma Separated values aldwinenriquez SQL Server 2005 3 June 1st, 2007 08:52 PM
comma separated list MunishBhatia SQL Server 2000 11 March 21st, 2007 03:56 PM
Comma Separated Column Value thru T-SQL subhendude SQL Server 2000 2 December 12th, 2006 01:42 PM
Select from Comma Separated Values sasidhar79 SQL Server 2000 2 March 28th, 2005 06:58 PM
SELECT query combining two field ids funkedup SQL Language 2 September 30th, 2004 12:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.