Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select statement - one to many relationship

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

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?
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 2010-02-05 : 10:48:12
well if I have more that two associated file to one job I would like to get this:

Job 1
22/02/2010
http://file1.doc
http://file2.doc
http://file3.doc
http://file4.doc

------------------------------

The Problem is that my current code would return this:

Job 1
22/02/2010
http://file1.doc

Job 1
22/02/2010
http://file2.doc

Job 1
22/02/2010
http://file3.doc

Job 1
22/02/2010
http://file4.doc

Go to Top of Page

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?
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 2010-02-05 : 10:58:35
why not, I am open to any suggestions.
Go to Top of Page

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?
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 2010-02-05 : 11:17:32
I am on SQL server 2005
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 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

Go to Top of Page

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 2
Line 2: Incorrect syntax near 'XML'.
Go to Top of Page

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 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'
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 2010-02-05 : 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






Go to Top of Page

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_dbcmptlevel
any ways since your SERVERPROPERTY('ProductVersion') returns 8 its SQL 2000
FOR XML PATH wont work in sql 2000
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 2010-02-05 : 11:47:55
2000 ! does this mean that I am "stuffed" ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 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
Go to Top of Page

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
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 2010-02-05 : 11:54:13
Brilliants , thanks you for your help and your time.

Chris
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 11:54:51
welcome
Go to Top of Page
   

- Advertisement -