SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select statement - one to many relationship
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cgoasduff
Starting Member

21 Posts

Posted - 02/05/2010 :  10:29:07  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
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
52249 Posts

Posted - 02/05/2010 :  10:43:20  Show Profile  Reply with Quote
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 - 02/05/2010 :  10:48:12  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 02/05/2010 :  10:52:23  Show Profile  Reply with Quote
you mean you want them delimited on same column?
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 02/05/2010 :  10:58:35  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
why not, I am open to any suggestions.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/05/2010 :  11:04:06  Show Profile  Reply with Quote
Ok Great and are you using sql 2005 or above?
Go to Top of Page

cgoasduff
Starting Member

21 Posts

Posted - 02/05/2010 :  11:17:32  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
I am on SQL server 2005
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/05/2010 :  11:22:08  Show Profile  Reply with Quote
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 - 02/05/2010 :  11:29:26  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 02/05/2010 :  11:33:50  Show Profile  Reply with Quote
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 - 02/05/2010 :  11:43:14  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 02/05/2010 :  11:46:21  Show Profile  Reply with Quote
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 - 02/05/2010 :  11:47:55  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
2000 ! does this mean that I am "stuffed" ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/05/2010 :  11:50:26  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 02/05/2010 :  11:51:25  Show Profile  Reply with Quote
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 - 02/05/2010 :  11:54:13  Show Profile  Visit cgoasduff's Homepage  Reply with Quote
Brilliants , thanks you for your help and your time.

Chris
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/05/2010 :  11:54:51  Show Profile  Reply with Quote
welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000