CODEWITHSUNDEEP
mysqlsql
papacico
papacico

Reputation: 169

CONCAT() not returning requested string

With this query, I want a name comprised of three fields to be returned. I don't understand why the concat() function is not returning all the requested values in the table. Some records will be filled--let `s say like 1-2-3. The result should then be "1 2 3". If the 3 records are 1-0-3 than the result should be "1 3." If the records are 1-2-0 then the result should be "1 2."

The three requested fields are all VARCHAR format.

See my results here The first and second records are wrong, the third and fourth are right, then, again, some are right and some are wrong.

SELECT pt_rooms.room_id, pt_rooms.room_hotel_id ,
a_room_type.room_type_name, 
pt_rooms.room_title, a_room_type_suffix.room_type_suffix_name ,    
CONCAT(a_room_type.room_type_name,' ', pt_rooms.room_title,' ',   
a_room_type_suffix.room_type_suffix_name ) 
AS long_name FROM `pt_rooms` 
LEFT JOIN a_room_type_suffix ON   
a_room_type_suffix.room_type_suffix_id=pt_rooms.room_type_suffix_id 
JOIN a_room_type ON a_room_type.room_type_id=pt_rooms.room_type;

Upvotes: 0

Views: 181

Answers (1)

gen_Eric
gen_Eric

Reputation: 227240

CONCAT() will return NULL if any of the values passed to it are NULL.

To get around this, you can use CONCAT_WS():

CONCAT_WS(' ', a_room_type.room_type_name, pt_rooms.room_title, a_room_type_suffix.room_type_suffix_name) AS long_name

Upvotes: 3

Related Questions