Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

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:


    '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.

1 Answer

0 votes
by (40.7k points)

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`



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

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

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 6, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer

Browse Categories