[SOLVED] joining columns from one table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

[SOLVED] joining columns from one table

Post 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?
Last edited by andym01480 on Thu Nov 23, 2006 11:12 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL has a function called CONCAT_WS().

http://dev.mysql.com/doc/refman/4.1/en/ ... #id2839659
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're asking it to concatenate strings, not field references. You don't need the single quotes around their names.
Post Reply