左加入 Where 子句 [英] Left Join With Where Clause

问题描述

我需要从设置表中检索所有默认设置，但如果存在 x 字符，还需要获取字符设置.

I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character.

但此查询仅检索字符 = 1 的那些设置，如果用户尚未设置任何人，则不会检索默认设置.

But this query is only retrieving those settings where character is = 1, not the default settings if the user havent setted anyone.

SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT JOIN `character_settings` 
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1'  

所以我需要这样的东西:

So i should need something like this:

array(
    '0' => array('somekey' => 'keyname', 'value' => 'thevalue'),
    '1' => array('somekey2' => 'keyname2'),
    '2' => array('somekey3' => 'keyname3')
)

当key 0 包含默认值和字符值时，其中key 1 和2 是默认值.

Where key 1 and 2 are the default values when key 0 contains the default value with the character value.

推荐答案

where 子句过滤掉 left join 不成功的行.将其移至联接:

The where clause is filtering away rows where the left join doesn't succeed. Move it to the join:

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  

这篇关于左加入 Where 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！