Two table concatenate issue?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


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
( )..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` =

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 :-

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

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 :-


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.


A West

Re: Two table concatenate issue?

Andrew wrote:
Quoted text here. Click to load it

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:

  FROM property_inventory AS P
    LEFT OUTER JOIN property_imagelist AS I1
    LEFT OUTER JOIN property_imagelist AS I2
    LEFT OUTER JOIN property_imagelist AS I3

(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.

Bill K.

Site Timeline