I need to retrieve all default settings from the settings table, but also need to get the character settings if the x character is present.
But this query only retrieves settings with character = 1, if the user has not set anything, the default settings will not be retrieved.
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')
)
Where, when key 0 contains a default value with a character value, keys 1 and 2 are the default values.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
When doing an OUTER JOIN (ANSI-89 or ANSI-92), the filter position is important because the conditions specified in the ON
clause are applied before doing the JOIN.After the JOIN is established the conditions provided in the WHEREclause for the OUTER JOINed table will be applied. This may produce very different result sets. In contrast, it doesn't matter for an INNER JOIN if you provide the condition in theONorWHEREclause - the result will be the same.SELECT s.*, cs.`value` FROM SETTINGS s LEFT JOIN CHARACTER_SETTINGS cs ON cs.setting_id = s.id AND cs.character_id = 1The
whereclause will filter out rows whereleft joinwas unsuccessful. Move it to connection:SELECT `settings`.*, `character_settings`.`value` FROM `settings` LEFT JOIN `character_settings` ON `character_settings`.`setting_id` = `settings`.`id` AND `character_settings`.`character_id` = '1'