左联接与where子句 [英] Left Join With Where Clause
本文介绍了左联接与where子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要从设置表中检索所有默认设置,而且还需要获取字符设置(如果存在x字符).
I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character.
但是此查询仅检索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')
)
当键0包含带有字符值的默认值时,键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屋!
查看全文