Where clause used in left join-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

Where clause used in left join

P粉439804514 2023-10-11 12:46:28

484

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.

P粉439804514

reply all(2)

P粉5855417662023-10-12 11:03:03 2 floor

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

Like +0

Add Reply

P粉9577231242023-10-12 00:19:46 1 floor

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'

Like +0

Add Reply