Page 1 of 1

[SOLVED] joining columns from one table

Posted: Thu Nov 23, 2006 10:02 am
by andym01480
I have a MYSQL database table called address with columns surname, addressname,add1,add2,town,county,postcode...

Code: Select all

SELECT surname,addressname,add1,add2,town,county,postcode FROM address
Is it possible to query it so that add1,add2,town,county,postcode could be returned as 'address' formatted "add1, add2, town, county, postcode"? Further can it ignore an empty add2 field?

Posted: Thu Nov 23, 2006 10:08 am
by feyd
MySQL has a function called CONCAT_WS().

http://dev.mysql.com/doc/refman/4.1/en/ ... #id2839659

Posted: Thu Nov 23, 2006 11:02 am
by andym01480
Thanks, but I can't get it working right!

Code: Select all

SELECT surname,addressname,children, CONCAT_WS(', ','add1','add2','town','county','postcode') AS 'add',phone,mobile,email,email2 FROM address  ORDER BY surname"
returns the other field contents correctly but "add1, add2, town, county, postcode" for add - the column names!

What have I done wrong

Posted: Thu Nov 23, 2006 11:05 am
by feyd
You're asking it to concatenate strings, not field references. You don't need the single quotes around their names.