MySQL 左连接，如果为空，则从一个或另一个中选择一个字段

发布于 2024-11-28 09:31:47 字数 842 浏览 0 评论 0原文

我正在尝试 LEFT JOIN 2 个表。效果很好。但我得到了两组名为 setting_value 的字段。仅当 tblAgencySettings.setting_value 为 NULL 时，我才尝试获取 tblSettings.setting_value。我该怎么做呢？我知道我可以重命名字段，然后在 PHP 中我可以检查 tblAgencySettings.setting_value 如果为 NULL，则获取 tblSettings.settings_value 但我更喜欢将其保留在 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'

我刚刚注意到的一个小问题。我没有提到这一点。如果 tblAgencySettings.setting_value 确实有值。但changeable不是1那么只需选择tblSettings.setting_value

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眉目亦如画i 2024-12-05 09:31:47

只需添加一个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'

COALESCE函数返回您赋予它的第一个非 NULL 值，因此：

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

如果该值不为 NULL，则将为 tblAgencySettings.setting_value 并且如果tblAgencySettings.setting_value为NULL，则tblSettings.setting_value。

如果 tblAgencySettings.setting_value 也可以为零，并且您想忽略它以及 NULL，那么您可以使用它来代替上面的 COALESCE：

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

IF 如果第一个参数为 true 并且第三，如果第一个参数为 false，因此上述使用将零转换为 NULL。或者，您可以一直转到 CASE 声明：

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

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

回复收藏 0 原文

蓝戈者 2024-12-05 09:31:47

将您的 SQL 语句更改为：

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'

这是 MYSQL CASE 语句的链接您的参考。

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.

回复收藏 0 原文

~没有更多了~