| Author |
 Topic  |
|
|
cgoasduff
Starting Member
21 Posts |
 Posted - 02/05/2010 : 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
-------------
id
jobTitle
jobClosingDate
tbl_jobfiles
------------
vacancyRef (FK related to tbl_jobAdvert.id )
filePath
fileName
id
------------------------------
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.vacancyRef
This returns the following result.
Job 1
22/02/2010
http://file1.doc
Job 1
22/02/2010
http://file2.doc
what I would like as a result is:
Job 1
22/02/2010
http://file1.doc
http://file2.doc
Can anyone help please?
Thanks
Chris
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 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
India
47173 Posts |
Posted - 02/05/2010 : 10:52:23
|
| you mean you want them delimited on same column? |
|
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 02/05/2010 : 10:58:35
|
| why not, I am open to any suggestions. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 11:04:06
|
| Ok Great and are you using sql 2005 or above? |
|
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 02/05/2010 : 11:17:32
|
| I am on SQL server 2005 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 11:22:08
|
ok then use this
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 j
|
|
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 02/05/2010 : 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 2
Line 2: Incorrect syntax near 'XML'. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 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 2
Line 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 - 02/05/2010 : 11:43:14
|
Ok, when I run "SELECT SERVERPROPERTY('ProductVersion')" I get :
8.00.194
If I run "SELECT SERVERPROPERTY('ProductVersion') EXEC sp_cmptlevel 'recruitmentHub'" I get:
(1 row(s) affected)
Msg 2812, Level 16, State 62, Line 1
Could 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 1
Could not find stored procedure 'sp_cmptlevel'.
The query I used (which gives me the unwanted) result is:
select id =tblJA.id, jobTitle,jobClosingDate, vacancyRef, filePath
from tbl_jobAdvert tblJA INNER JOIN tbl_jobfiles tblJF ON tblJA.id = tblJF.vacancyRef
The 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 j
Thanks
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 11:46:21
|
sorry it was a typo should have been sp_dbcmptlevel
any ways since your SERVERPROPERTY('ProductVersion') returns 8 its SQL 2000
FOR XML PATH wont work in sql 2000 |
|
|
|
cgoasduff
Starting Member
21 Posts |
Posted - 02/05/2010 : 11:47:55
|
| 2000 ! does this mean that I am "stuffed" ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 11:50:26
|
you can this solution for 2000
CREATE FUNCTION dbo.ConcatFilePaths
(
@JobID int
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @FilePathList varchar(8000)
SELECT @FilePathList=COALESCE(@FilePathList+';' ,'') + filePath
FROM tbl_jobfiles
WHERE vacancyRef = @JobID
RETURN @FilePathList
END
then 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
India
47173 Posts |
Posted - 02/05/2010 : 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 - 02/05/2010 : 11:54:13
|
Brilliants , thanks you for your help and your time.
Chris |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/05/2010 : 11:54:51
|
welcome  |
|
|
| |
Topic |
|
select statement - one to many relationship
