MySQL Left Join, SELECT a field either from one or the other if null

Question

I am trying to LEFT JOIN 2 tables. which is working out fine. But i am getting back two sets of fields named setting_value. iam trying to get tblSettings.setting_value only if tblAgencySettings.setting_value is NULL. How would i go about doing this? I know i can rename the fields, then in PHP i can check the tblAgencySettings.setting_value and if NULL then grab the tblSettings.setting_value but i prefer to keep this at MySQL.

SELECT `tblSettings`.`id`, `tblSettings`.`setting_name`,
       `tblSettings`.`setting_value`, `tblAgencySettings`.`setting_value`
FROM `tblSettings` LEFT JOIN `tblAgencySettings` 
ON `tblSettings`.`id` = `tblAgencySettings`.`setting_id`
AND `tblAgencySettings`.`agency_id` = '1'
WHERE `tblSettings`.`changeable` = '1'

slight issue i just noticed. i failed to mention this. if tblAgencySettings.setting_value does have a value. but changeable is not 1 then just select tblSettings.setting_value

mu is too short · Accepted Answer · 2011-08-08 04:33:30Z

Just add a COALESCE:

SELECT `tblSettings`.`id`, `tblSettings`.`setting_name`,
       COALESCE(`tblAgencySettings`.`setting_value`, `tblSettings`.`setting_value`)
FROM `tblSettings` LEFT JOIN `tblAgencySettings` 
ON `tblSettings`.`id` = `tblAgencySettings`.`setting_id`
AND `tblAgencySettings`.`agency_id` = '1'
WHERE `tblSettings`.`changeable` = '1'

The COALESCE function returns the first non-NULL value you give it so this:

COALESCE(`tblAgencySettings`.`setting_value`, `tblSettings`.`setting_value`)

Will be tblAgencySettings.setting_value if that's not NULL and tblSettings.setting_value if tblAgencySettings.setting_value is NULL.

If tblAgencySettings.setting_value can also be zero and you want to ignore that as well as NULL, then you could use this instead of the COALESCE above:

COALESCE(
    IF(`tblAgencySettings`.`setting_value` = 0, NULL, `tblAgencySettings`.`setting_value`),
   `tblSettings`.`setting_value`
)

The IF returns the second argument if the first is true and the third if the first argument is false so the above use converts zero to NULL. Or, you could go all the way to a CASE statement:

case
    when `tblAgencySettings`.`setting_value` = 0     then `tblSettings`.`setting_value`
    when `tblAgencySettings`.`setting_value` IS NULL then `tblSettings`.`setting_value`
    else `tblSettings`.`setting_value`
end

Icarus · Accepted Answer · 2011-08-08 04:20:14Z

2

Change your SQL Statement to this:

SELECT `tblSettings`.`id`, `tblSettings`.`setting_name`, 
   CASE WHEN `tblSettings`.`setting_value` IS NULL THEN `tblAgencySettings`.`setting_value` 
    ELSE `tblSettings`.`setting_value` END AS `setting_value` 
    FROM `tblSettings` LEFT JOIN `tblAgencySettings` 
    ON `tblSettings`.`id` = `tblAgencySettings`.`setting_id`
    AND `tblAgencySettings`.`agency_id` = '1'
    WHERE `tblSettings`.`changeable` = '1'

Here's a link to MYSQL CASE Statement for your reference.

answered Aug 8, 2011 at 4:20

Icarus

64k14 gold badges102 silver badges116 bronze badges

Collectives™ on Stack Overflow

MySQL Left Join, SELECT a field either from one or the other if null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related