Left Join With Where Clause

Question

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

But this query is only retrieving those settings where the character is = 1, not the default settings if the user havent set 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')

)

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

Answer 1

The where clause is used to filter the rows where the left join will not succeed. 

Move it to the join and try using this:

SELECT  `settings`.*, `character_settings`.`value`

FROM    `settings`

LEFT JOIN 

       `character_settings` 

ON     `character_settings`.`setting_id` = `settings`.`id`

        AND `character_settings`.`character_id` = '1'