Jointure à gauche avec clause Where

Question

J'ai besoin de récupérer tous les paramètres par défaut de la table des paramètres, mais aussi de saisir le paramètre du caractère s'il existe pour x caractères.

Mais cette requête ne récupère que les paramètres où le caractère est = 1, et non les paramètres par défaut si l'utilisateur n'a rien défini.

SELECT `settings`.*, `character_settings`.`value`
FROM (`settings`)
LEFT JOIN `character_settings` 
ON `character_settings`.`setting_id` = `settings`.`id`
WHERE `character_settings`.`character_id` = '1'  

Je devrais donc avoir besoin de quelque chose comme ça :

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

Où les touches 1 et 2 sont les valeurs par défaut lorsque la touche 0 contient la valeur par défaut avec la valeur du caractère.

Answer 1

En where filtre les lignes où la clause left join ne réussit pas. Déplacez-le vers le joint :

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'

Answer 2

Lors de la création de OUTER JOINs (ANSI-89 ou ANSI-92), l'emplacement du filtrage est important car les critères spécifiés dans le fichier ON La clause est appliquée avant que la JOIN soit faite . Critères par rapport à une table OUTER JOINED fournie dans le formulaire de demande d'accès. WHERE La clause est appliquée après que le JOIN soit fait . Cela peut produire des ensembles de résultats très différents. En comparaison, il n'est pas important pour les INNER JOINs que les critères soient fournis dans la balise ON o WHERE le résultat sera le même.

  SELECT  s.*, 
          cs.`value`
     FROM SETTINGS s
LEFT JOIN CHARACTER_SETTINGS cs ON cs.setting_id = s.id
                               AND cs.character_id = 1

Answer 3

Si je comprends bien votre question, vous voulez les enregistrements de la base de données des paramètres s'ils n'ont pas de jointure avec la table character_settings ou si l'enregistrement joint a character_id = 1.

Vous devez donc faire

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

Answer 4

Vous trouverez peut-être plus facile à comprendre en utilisant une sous-requête simple.

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`

La sous-requête est autorisée à renvoyer un résultat nul, de sorte que vous n'avez pas à vous préoccuper de JOIN/WHERE dans la requête principale.

Parfois, cela fonctionne plus rapide dans MySQL, mais comparez-la à la forme LEFT JOIN pour voir ce qui fonctionne le mieux pour vous.

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

Answer 5

Pour ce problème, comme pour beaucoup d'autres impliquant des jointures gauches non triviales, telles que les jointures gauches sur des tables jointes à l'intérieur, je trouve qu'il est plus pratique et un peu plus lisible de diviser la requête à l'aide d'une balise with clause. Dans votre exemple,

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;