Where clause used in left join

Question

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.

Answer 1

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 WHERE clause 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 the ON or WHERE clause - 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 = 1

Answer 2

The

where clause will filter out rows where left join was 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'