MYSQL concat with sub query

Question

I need to concatenate the address fields (zip,city,countryName, streetAddress) of my table. This is the query i wrote,

SELECT id,concat_ws(' ',address1,zip,city,(select countryName from country where countryCode = User.countryCode))  FROM User

And it provides me 28 Avenue Pasteur 14390 Cabourg France which should actually be, 27 Avenue Pasteur, 14390 Cabourg, France (with commas separated)

How can i do achieve this?

Update when used SELECT id,concat_ws(',',address1,zip,city,(select countryName from country where countryCode = User.countryCode)) FROM User

it gives 27 Avenue Pasteur, 14390, Cabourg, France

but not 27 Avenue Pasteur, 14390 Cabourg, France (no comman in between 14390 Cabourg)

@GordonLinoff it gives me 28 Avenue Pasteur,14390,Cabourg,France but not 27 Avenue Pasteur, 14390 Cabourg, France — dev1234
– dev1234, Commented Jul 28, 2015 at 10:57

Raging Bull · Accepted Answer · 2015-07-28 11:03:41Z

1

Try this way (also, it is better to use join):

select u.id,concat(u.address1,', ',u.zip,' ',u.city,', ',c.countryName) as Address
from User u join
     country c on u.countryCode=c.countryCode

The result will be:

27 Avenue Pasteur, 14390 Cabourg, France

edited Jul 28, 2015 at 11:03

answered Jul 28, 2015 at 10:56

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dev1234 Over a year ago

This isnt giving the address as output. SELECT u.id, u.address1 + ', ' + u.zip + ' ' + u.city + ', ' + c.countryName FROM User u JOIN country c ON u.countryCode = c.countryCode

dev1234 Over a year ago

The output is some numbers like 14418 under the column u.address1+', '+u.zip+' '+u.city+', '+c.countryName

Raging Bull Over a year ago

@dev1234: Updated my answer. Try it now.

dev1234 Over a year ago

yes its working sorry. i was trying to sort out problem and forgot to accept the answer. stackoverflow.com/questions/31675410/…

john igneel · Accepted Answer · 2015-07-28 10:56:44Z

1

You should add a comma as the delimiter of your string. Try this:

SELECT id,concat_ws(',',address1,zip,city,(select countryName from country where countryCode = User.countryCode))  FROM User;

answered Jul 28, 2015 at 10:56

john igneel

4171 gold badge6 silver badges18 bronze badges

1 Comment

dev1234 Over a year ago

I need the output as 27 Avenue Pasteur, 14390 Cabourg, France not as 27 Avenue Pasteur, 14390, Cabourg, France

Collectives™ on Stack Overflow

MYSQL concat with sub query

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related