263

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:

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

Improve this question
0

7 Answers 7

Reset to default
520

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` LEFT JOIN `character_settings` ON `character_settings`.`setting_id` = `settings`.`id` AND `character_settings`.`character_id` = '1'
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I would like to add that the conditional statement AND acts as a WHERE clause, then it only applies to the character_settings table. We can use such a setting in situations when we want to filter tables before joining them. Moreover, as you mentioned above, if we use an explicit WHERE clause the filter happens after the tables are joined.
99

When making OUTER JOINs (ANSI-89 or ANSI-92), filtration location matters because criteria specified in the ON clause is applied before the JOIN is made. Criteria against an OUTER JOINed table provided in the WHERE clause is applied after the JOIN is made. This can produce very different result sets. In comparison, it doesn't matter for INNER JOINs if the criteria is provided in the ON or WHERE clauses -- 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
Improve this answer

Comments

31

If I understand your question correctly you want records from the settings database if they don't have a join accross to the character_settings table or if that joined record has character_id = 1.

You should therefore do

SELECT `settings`.*, `character_settings`.`value` FROM (`settings`) LEFT OUTER JOIN `character_settings` ON `character_settings`.`setting_id` = `settings`.`id` WHERE `character_settings`.`character_id` = '1' OR `character_settings`.character_id is NULL
Improve this answer

2 Comments

That risks returning false positives
@OMGPonies I don't understand, What might be the cases that it has risks. In my case I applied the AND with join and no result was there, but when I used above solution, I had results. But I want to be sure about the issues I can face with this option.
5

You might find it easier to understand by using a simple subquery

SELECT `settings`.*, ( SELECT `value` FROM `character_settings` WHERE `character_settings`.`setting_id` = `settings`.`id` AND `character_settings`.`character_id` = '1') AS cv_value FROM `settings`

The subquery is allowed to return null, so you don't have to worry about JOIN/WHERE in the main query.

Sometimes, this works faster in MySQL, but compare it against the LEFT JOIN form to see what works best for you.

SELECT s.*, c.value FROM settings s LEFT JOIN character_settings c ON c.setting_id = s.id AND c.character_id = '1'
Improve this answer

Comments

4

The way I finally understand the top answer is realising (following the Order Of Execution of the SQL query ) that the WHERE clause is applied to the joined table thereby filtering out rows that do not satisfy the WHERE condition from the joined (or output) table. However, moving the WHERE condition to the ON clause applies it to the individual tables prior to joining. This enables the left join to retain rows from the left table even though some column entries of those rows (entries from the right tables) do not satisfy the WHERE condition.

Improve this answer

1 Comment

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
3

For this problem, as for many others involving non-trivial left joins such as left-joining on inner-joined tables, I find it convenient and somewhat more readable to split the query with a with clause. In your example,

with settings_for_char as ( select setting_id, value from character_settings where character_id = 1 ) select settings.*, settings_for_char.value from settings left join settings_for_char on settings_for_char.setting_id = settings.id;
Improve this answer

Comments

-1

The result is correct based on the SQL statement. Left join returns all values from the right table, and only matching values from the left table.

ID and NAME columns are from the right side table, so are returned.

Score is from the left table, and 30 is returned, as this value relates to Name "Flow". The other Names are NULL as they do not relate to Name "Flow".

The below would return the result you were expecting:

 SELECT a.*, b.Score FROM @Table1 a LEFT JOIN @Table2 b ON a.ID = b.T1_ID WHERE 1=1 AND a.Name = 'Flow'

The SQL applies a filter on the right hand table.

Improve this answer

1 Comment

the sentence "Left join returns all values from the right table" is incorrect. Left join returns all values from the LEFT table

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.