Two Table clever concatenate (but too clever for me!)

[amb4rsil]

Hello,

Rather complicated one im afraid.. (well for me anyway!) Background on this one is that I have inherited a system that needs some modification, have some basic sql knowledge but fear i've bitten off a little bit more than I can handle.

I need to export some data which I am able to do as I have some exporting software which can just tap into a mysql database. Its a fairly straightforward real estate database.

Table 1 (property_info) - pcode, name,add1,prevowner etc etc etc...

Table 2 (property_imagelist) - pcode, thumbnail, image

This database basically runs the backend part of a search/add php system..the usual stuff.

Thing is I need to export the data into a specific comma delimited format..which I can do fine very easily with MySQL Data Wizard (

http://www.sqlmaestro.com/products/mywizard)..thing

is the format that I need the csv needs to have all the info from the propery_info table, but also the image filenames stored in the image field in the property_images table.

Still with me? Hope so! So within the MySQL data wizard app I can write an SQL query off the database which it then can then create an csv file..perfect. Well so I thought until I tried to do it.

I can merge the two tables together very simply. just using

INNER JOIN `property_imagelist` ON (`property_inventory`.`pcode` = `vehicle_imagelist`.`pcode`)

This is perfect, all but one snag and this is where Im hoping someone here can help. Within the imagelist table there are multiple images. So for example :-

PCODE THUMBNAIL IMAGE
1534 8888tmb.jpg 8888.jpg
3244 1111tmb.jpg 1111.jpg
3244 11112tmb.jpg 11112.jpg
3244 11113tmb.jpg 11114.jpg
5335 1112tmb.jpg 1112.jpg


Ok, so we got multiple images for some (3244) but not for others (another problem) indeed some of them only have one pic (1534 and 5355)..

From a data export requirement, what I ideally need is all the fields from the property_inventory table PLUS (hopefully sql is able to do this) a concatenated or generated IMAGES field (apologies don't know the correct term) like this :-

PCODE IMAGES

1534 8888.jpg
3244 1111.jpg,11112tmb.jpg,11113tmb.jpg
5335 1112.jpg

NOTICE that its just the first image filename and then whatever thumbnail filenames exist. This will then give me a perfect export for my needs.

Any help on this would be absolutely amazing! Thanks in advance for anyone who is kind enough to look at this.

Regards,

Amb3rsil

 

[r937]

very, very simple solution -- the GROUP_CONCAT function

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[amb4rsil]

Thanks for this, unfortunately GROUP_CONCAT is only available in MySQL 4.1 and onwards, my ISP refuses to upgrade my hosting from 4.0 based on there being too many bugs in it, (despite my complaints that it is the recommended stable release).. Is there any other way?

Thanks again in advance for any help on this.

Amb3rsil

 

[r937]

nope, not with sql,

you'll have to run a simple ORDER BY query and do the concatenation in a scripting language like coldfusion or asp or php

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)