[FIXED] MYSQL concat with sub query

Issue

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)

Solution

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

Answered By - Raging Bull