CODEWITHSUNDEEP
mysqlleft-joinconcatenationinner-join
EternalHour
EternalHour

Reputation: 8621

Merging columns in INNER/LEFT JOIN returns NULL values?

I'm facing an issue where I have several tables joined, but I need to combine the e.url and f.url_new columns. Otherwise, the result is as expected. Here is my query.

SELECT a.`added_date`,b.`type`,c.`action`,e.`url`,f.`url_new` 
FROM `websites_submitted_main` a
INNER JOIN `websites_submitted_type` b ON a.`type_id` = b.`type_id` 
INNER JOIN `websites_submitted_action` c ON a.`action_id` = c.`action_id`
INNER JOIN `users` d ON a.`user_id` = d.`user_id`
LEFT JOIN `websites` e ON a.`url_id` = e.`id`
LEFT JOIN `websites_submitted_new` f ON a.`url_new_id` = f.`url_new_id`
WHERE a.`user_id` = 1 ORDER BY a.`added_date` DESC

I've tried CONCAT, but the column contained all NULL values. Here's that query.

SELECT a.`added_date`,b.`type`,c.`action`,CONCAT(e.`url`,f.`url_new`) AS url 
FROM `websites_submitted_main` a
INNER JOIN `websites_submitted_type` b ON a.`type_id` = b.`type_id` 
INNER JOIN `websites_submitted_action` c ON a.`action_id` = c.`action_id`
INNER JOIN `users` d ON a.`user_id` = d.`user_id`
LEFT JOIN `websites` e ON a.`url_id` = e.`id`
LEFT JOIN `websites_submitted_new` f ON a.`url_new_id` = f.`url_new_id`
WHERE a.`user_id` = 1 ORDER BY a.`added_date` DESC

Is there a minor modification I can make to this query to merge these columns?

Upvotes: 0

Views: 115

Answers (1)

Jaylen
Jaylen

Reputation: 40289

try using CONCAT_WS() instead of CONCAT()

SELECT  `CONCAT_WS(' ',e.url, f.url_new )` ....

CONCAT_WS() will concatenate the values if the values are not null.

From the manual of CONCAT()

CONCAT() returns NULL if any argument is NULL.

SELECT a.`added_date`,b.`type`,c.`action`,CONCAT_WS("", e.`url`,f.`url_new`) AS url 
FROM `websites_submitted_main` a
INNER JOIN `websites_submitted_type` b ON a.`type_id` = b.`type_id` 
INNER JOIN `websites_submitted_action` c ON a.`action_id` = c.`action_id`
INNER JOIN `users` d ON a.`user_id` = d.`user_id`
LEFT JOIN `websites` e ON a.`url_id` = e.`id`
LEFT JOIN `websites_submitted_new` f ON a.`url_new_id` = f.`url_new_id`
WHERE a.`user_id` = 1 ORDER BY a.`added_date` DESC

Upvotes: 2

Related Questions