Author |
Topic |
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 10:29:07
|
Hi there, I am having an issue with the following.I have two tables. "tbl_jobAdvert" which hold the informations about a job and "tbl_jobfiles" which hold data about the filepath where users can download Job specs, contracts etc..tbl_jobAdvert-------------idjobTitlejobClosingDatetbl_jobfiles------------vacancyRef (FK related to tbl_jobAdvert.id )filePathfileNameid------------------------------Here is my sql statement:select id =tblJA.id, jobTitle,jobClosingDate, vacancyRef, filePath from tbl_jobAdvert tblJA INNER JOIN tbl_jobfiles tblJF ON tblJA.id = tblJF.vacancyRefThis returns the following result.Job 122/02/2010http://file1.docJob 122/02/2010http://file2.docwhat I would like as a result is:Job 122/02/2010http://file1.dochttp://file2.docCan anyone help please?ThanksChris |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 10:43:20
|
what about cases where you've more than two associated files? in that case what all you want to return? |
|
|
cgoasduff
Starting Member
21 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 10:52:23
|
you mean you want them delimited on same column? |
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 10:58:35
|
why not, I am open to any suggestions. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:04:06
|
Ok Great and are you using sql 2005 or above? |
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 11:17:32
|
I am on SQL server 2005 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:22:08
|
ok then use thisSELECT j.id,j.jobTitle,j.jobClosingDate,STUFF((SELECT ';' + filePath FROM tbl_jobfiles WHERE vacancyRef=j.id FOR XML PATH('')),1,1,'')FROM tbl_jobAdvert j |
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 11:29:26
|
Hello Visakh Thanks for this, I tried the select statement in SQL Server Management Express and I get the following error:Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'XML'. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:33:50
|
quote: Originally posted by cgoasduff Hello Visakh Thanks for this, I tried the select statement in SQL Server Management Express and I get the following error:Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'XML'.
can you show your used query? also what does below return?SELECT SERVERPROPERTY('ProductVersion')EXEC sp_cmptlevel 'your db name here' |
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 11:43:14
|
Ok, when I run "SELECT SERVERPROPERTY('ProductVersion')" I get :8.00.194If I run "SELECT SERVERPROPERTY('ProductVersion') EXEC sp_cmptlevel 'recruitmentHub'" I get: (1 row(s) affected)Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp_cmptlevel'.If I run "EXEC sp_cmptlevel 'recruitmentHub'" on its own I get: Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'sp_cmptlevel'.The query I used (which gives me the unwanted) result is:select id =tblJA.id, jobTitle,jobClosingDate, vacancyRef, filePathfrom tbl_jobAdvert tblJA INNER JOIN tbl_jobfiles tblJF ON tblJA.id = tblJF.vacancyRefThe query that you suggested which returns the Line 2: Incorrect syntax near 'XML is:SELECT j.id,j.jobTitle,j.jobClosingDate,STUFF((SELECT ';' + filePath FROM tbl_jobFiles WHERE vacancyRef=j.id FOR XML PATH('')),1,1,'')FROM tbl_jobAdvert jThanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:46:21
|
sorry it was a typo should have been sp_dbcmptlevelany ways since your SERVERPROPERTY('ProductVersion') returns 8 its SQL 2000FOR XML PATH wont work in sql 2000 |
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 11:47:55
|
2000 ! does this mean that I am "stuffed" ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:50:26
|
you can this solution for 2000CREATE FUNCTION dbo.ConcatFilePaths(@JobID int)RETURNS varchar(8000)ASBEGINDECLARE @FilePathList varchar(8000)SELECT @FilePathList=COALESCE(@FilePathList+';' ,'') + filePath FROM tbl_jobfiles WHERE vacancyRef = @JobIDRETURN @FilePathListENDthen use it like SELECT j.id,j.jobTitle,j.jobClosingDate,dbo.ConcatFilePaths(j.id)FROM tbl_jobAdvert j |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:51:25
|
quote: Originally posted by cgoasduff 2000 ! does this mean that I am "stuffed" ?
Nope not quite You can use my last posted suggestion |
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 2010-02-05 : 11:54:13
|
Brilliants , thanks you for your help and your time.Chris |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-05 : 11:54:51
|
welcome |
|
|
|