The following will do what you need, although it is a little long-winded.
-- Prepare blank list
declare @list varchar(4000)
set @list = ''
-- Get SOFTWARE list for COMPANY
select @list = @list + ',' + SOFTWARE.SOFTWARE_NAME
from SOFTWARE
inner join COMPANY_SOFTWARES ON
COMPANY_SOFTWARES.SOFTWARE_ID = SOFTWARE.SOFTWARE_ID
where COMPANY_SOFTWARES.COMPANY_ID = 10
-- Removes Extraneous , at beginning
select @list = substring( @list , 2, len( @list ) )
-- Final Returned Query
select COMPANY.COMPANY_ID,
COMPANY.COMPANY_name,
@list as SOFTWARES
from COMPANY
where COMPANY_ID = 10
The other option is do it through ColdFusion as suggested, which may be a
tidier way.
Gavin
-----Original Message-----
From: Nurettin Omer Hamzaoglu [mailto:[EMAIL PROTECTED]
Sent: 03 November 2004 10:29
To: CF-Talk
Subject: MS SQL problem
Hi all,
I've 3 tables COMPANY, COMPANY_SOFTWARES, SOFWARES. COMPANY_SOFTWARES table
holds the data of the softwares used in the companies like;
....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF
community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=34
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183245
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
