Sein Kraft Sein Kraft - 1 year ago 96
SQL Question

Left Join With Where Clause

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 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:

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

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

Answer Source

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`
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download