Two table concatenate issue?

[Andrew]

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,

A West

[Bill Karwin]

Andrew wrote:

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

I don't believe this can be done in the general case (i.e. an unknown
number of images for a given pcode) in a single SQL statement, without
using a stored procedure or user-defined function.

I think you could do it using outer joins, if you could assume a finite
limit to the number of images per pcode, but this isn't a general
solution. For instance, here's a solution for up to three images per pcode:

SELECT P.PCODE,
CONCAT_WS(',', I1.IMAGE, I2.IMAGE, I3.IMAGE) AS IMAGES
FROM property_inventory AS P
LEFT OUTER JOIN property_imagelist AS I1
ON P.PCODE = I1.PCODE
LEFT OUTER JOIN property_imagelist AS I2
ON P.PCODE = I2.PCODE AND I1.IMAGE < I2.IMAGE
LEFT OUTER JOIN property_imagelist AS I3
ON P.PCODE = I3.PCODE AND I2.IMAGE < I3.IMAGE;

(caveat: I have not tested this query)

CONCAT_WS() is a MySQL function that concatenates its arguments
together, separated by the first argument (',' in this case), and it
skips any NULL arguments.
See http://dev.mysql.com/doc/mysql/en/string-functions.html.

Regards,
Bill K.