• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

ok i give up on this.

Explorer ,
Jun 24, 2010 Jun 24, 2010

Copy link to clipboard

Copied

i have 2 simple tables

1 - cars

id     make     model

1     toyota     corolla

2 - car_images

rel_car_id     image

1                    front.jpg

1                    back.jpg

i need a 1 line query with the images all in 1 field (comma seperated)

eg

1     toyota     corolla     front,jpg,back.jpg

ive tried complex sql, valuelist function and everything has be beat!

im stuck and woudl love your help.

ps my final goal is to take this new query and dump it to a csv.

TOPICS
Advanced techniques

Views

932

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jun 24, 2010 Jun 24, 2010

Copy link to clipboard

Copied

Something like this?

SELECT c.make, c.model, i.image

FROM cars c

LEFT JOIN car_images i

ON c.id = i.rel_car_id

WHERE c.id = 1

Ken Ford

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 24, 2010 Jun 24, 2010

Copy link to clipboard

Copied

DECLARE @tmpstr varchar(250) -- or max or whatever

SELECT c.make, c.model, @tmpstr = COALESCE( @tmpstr + ',' , '') + i.image as

'images'

FROM cars c

LEFT JOIN car_images i

ON c.id = i.rel_car_id

WHERE c.id = 1

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 25, 2010 Jun 25, 2010

Copy link to clipboard

Copied

yui8979 thanks,

I am getting close, im am not skilled on this and this looks like a stored procedure?

Where do i put this to run this.

I get an error when i create a new proceduure in SQL 2005 express.

Is there a way I can code this from the coldfusion page?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 25, 2010 Jun 25, 2010

Copy link to clipboard

Copied

yes you simply put that whole chunk into your cfquery. Edit : There was a minor error in the previous solution

<cfquery name="test" datasource="your-datasource-here">

DECLARE @tmpstr varchar(250) -- or max or whatever

DECLARE @make varchar(250)

DECLARE @model varchar(250)

SELECT @make=c.make, @model=c.model, @tmpstr = COALESCE( @tmpstr + ',' , '') +  i.image as 'images'

FROM cars c

LEFT JOIN car_images i

ON c.id = i.rel_car_id

WHERE c.id = 1

SELECT @make,@model,@tmpstr

</cfquery>

Message was edited by: yui8979

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 25, 2010 Jun 25, 2010

Copy link to clipboard

Copied

Haha sorry, my bad. My code gives you an error.

Anyway, you can also do what Ken suggested :


<cfquery name="test" datasource="your-datasource">
SELECT c.id, c.make, c.model, i.image
FROM cars c
LEFT JOIN car_images i
ON c.id = i.rel_car_id
WHERE c.id = 1
order by c.id
</cfquery>

<cfoutput query=test group=id>
#id#,#make#,#model#<cfoutput>,#image#</cfoutput>
</cfoutput>

Message was edited by: yui8979

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jul 19, 2010 Jul 19, 2010

Copy link to clipboard

Copied

Thanks guys and sorry for my delay in returning to you.

I had to push the data to a csv file so I had to do the code at the source (sql server).

So its actually not really a CF query at all.

However here is the sql 2005 code that worked a treat, seems OTT for my liking but it works..

SELECT     VEHICLES.ID AS VEHICLEID, '' AS REGISTRATION, MAKES.MAKE, VEHICLES.MODEL, '"' + REPLACE(VEHICLES.SUMMARY, '''', '\"') + '"' AS TITLE,
                      '' AS TRANSMISSION, VEHICLES.FUEL, '' AS BODYSTYLE, VEHICLES.PRICE, VEHICLES.YEAR, VEHICLES.MILEAGE, 0 AS DOORS, '' AS COLOUR,
                      '' AS INSGRP, '' AS LOCATION, '"' + REPLACE(VEHICLES.DESCRIPTION, '''', '\"') + '"' AS DESCRIPTION, IMAGES = '"' + REPLACE
                          ((SELECT DISTINCT 'http://www.clivehamiltonmotors.com/images/vehicles/' + IMAGE + ',' AS [data()]
                              FROM         VEHICLE_IMAGES
                              WHERE     REL_VEHICLE_ID = VEHICLES.ID FOR XML PATH('')), '', '-') + '"'
FROM         VEHICLES INNER JOIN
                      MAKES ON VEHICLES.MAKE = MAKES.ID

thanks again.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jul 20, 2010 Jul 20, 2010

Copy link to clipboard

Copied

LATEST

Since you're using SQL Server, if you need to do this sort of thing in the future take a look at .NET CLR functions.  You can find a sample online for a concatenate aggregate function that does exactly what you're looking for.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation