Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Two table concatenate issue?
March 27, 2005, 11:23 pm
rate this thread
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
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 :-
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
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 :-
NOTICE that its just the first image filename and then whatever
thumbnail filenames exist. This will then give me a perfect export for
Any help on this would be absolutely amazing! Thanks in advance for
anyone who is kind enough to look at this.
Re: Two table concatenate issue?
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:
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.
- » I just ran /usr/bin/mysql_install_db and it told me to set a password for the MySQL root U...
- — Previous thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum