7 Replies Latest reply on Jul 20, 2010 11:02 AM by insuractive

    ok i give up on this.

    advancesolutions

      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.

        • 1. Re: ok i give up on this.
          Ken Ford - Fordwebs, LLC

          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

          • 2. Re: ok i give up on this.
            yui8979 Level 1

            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

            • 3. Re: ok i give up on this.
              advancesolutions Level 1

              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?

              • 4. Re: ok i give up on this.
                yui8979 Level 1

                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

                1 person found this helpful
                • 5. Re: ok i give up on this.
                  yui8979 Level 1

                  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

                  1 person found this helpful
                  • 6. Re: ok i give up on this.
                    advancesolutions Level 1

                    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.

                    • 7. Re: ok i give up on this.
                      insuractive Level 3

                      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.