Query Question

[f0z]

I've a db with a many-many relationship.

Very simple implementation with just 3 tables.

tblSoftware[smallint sid, varchar sname]
This table just holds all the software titles.

tblImages[smallint iid, varchar iname]
This table just holds all of the different images that we have in the company.

tblJunction[smallint iid, smallint iid]
This table is used to implement the many to many relationship. As an image can hold any number of software items and a software item can be on any number of images.

That out of the way my question is:
I want to generate a query that will return to me each software item and all of the images that it's associated with.

I've used this:

select s.name, jun.iid from tblSoftware s inner join tblJunction jun on s.sid = jun.sid

This is fine but it returns a different row for each image whereas I'd rather a "collection" of images in just the one column. If this is possible.

Any help is greatly appreciated. If you need more details dont hesitate. Cheers foz

 

[hneal98]

If you are familiar with functions, here is one I used for a project. You can modify it to work with your project:

CREATE function [dbo].fn_ConcatProc(@id varchar(15))
RETURNS varchar(7000) AS 

--This function concatenates the ICD9 Procedure Code (HK 10/15/2003) 

BEGIN 
declare @output_string varchar(7000)
set	@output_string = ''
	Select 
		@output_string = rtrim(@output_string) + case when @output_string > '' then ','  else ''   end   + rtrim(cp.pcode)
	from plandata..claimproc cp
	where claimid = @id
return @output_string 
END

the @id that is passed would be the data you want added to one column.