[RÉSOLU] Aide pour une requete MySQL

Aide pour une requete MySQL [RÉSOLU] - SQL/NoSQL - Programmation

Marsh Posté le 03-07-2007 à 18:00:06    

Hello, j'ai un petit casse tête.
Quand j'étais étudiant, je faisais ça courament, mais là, plus moyen de trouver la syntaxe.
J'ai 3 tables (j'ai supprimé les colonnes qui ne sont pas nécessaires):
Utilisateurs : id, login;
1, marcel
2, bob
3, rémy
3, gérard
 
Sites : id, nom;
1, siteA
2, siteB
 
Autorisations : id, idutilisateur, idsite;
1, 1(marcel), 1(siteA)
2, 3(rémy), 2(siteB)
3, 1(marcel), 2(siteB)
 
marcel peut modifier le site A et B, rémy peut modifier le site B. Bob et gérard ne peuvent modifier aucun site.
Vous comprennez que les utilisateurs ayants droits de modifier tel ou tel site sont listés dans la table autorisations.
Je peux par exemple sélectionner les utilisateurs qui ont le droit de modifier le site avec l'id 1 avec cette requête :

Code :
  1. SELECT u.* FROM utilisateurs u, autorisations a WHERE a.idutilisateur=u.id AND a.idsite = 1;


 
ce qui me donnerai marcel.
 
Pas de soucis.
Maintenant, j'aimerai créer une liste d'utilisateurs à ajouter aux utilisateurs ayant le droit d'utiliser un site. En gros, deux listes, une qui comprend les utilisateurs déjà autorisés pour ce site, et une qui comprends les utilisateurs qui ne sont pas encore autorisés à modifier ce site.
La première liste ne pose pas de problème, la deuxième par contre...
 
Je n'arrive pas à sélectionner tous les utilisateurs sauf ceux qui sont déjà autorisés à modifier cette table.
En gros, si j'édite les droits du siteA, ça devrait me donner les ids et logins de bob, rémy et gérard. Si j'édite les droits du site B, ça devrait me donner les id et logins de bob et gérard.
 
Si quelqu'un peut m'aider à trouver la requete SQL.
Je partais sur

Code :
  1. SELECT u.id as id, u.login FROM utilisateurs u LEFT JOIN autorisations a ON a.idutilisateur = u.id WHERE idsite != 1


mais ça ne donne pas du tout le bon résultat.


Message édité par Milhooz le 04-07-2007 à 08:59:41

---------------
Emprint - Création de Sites Web
Reply

Marsh Posté le 03-07-2007 à 18:00:06   

Reply

Marsh Posté le 03-07-2007 à 21:02:07    

1/ Le champ "id" dans ta table de jointure est inutile et apporte un problème : obligé d'éjouter une contrainte supplémentaire pour vérifier l'unicité des deux colonnes (idutilisateur, idsite). Il doit donc être supprimé au profit d'une clé composite sur (idutilisateur, idsite).
 
2/ T'es sur la bonne piste, mais c'est pas tout à fait ça ;)
 
Je suis bien d'accord que c'est pas très clair, et c'est pour ça que je vais t'expliquer plutôt que de te donner la réponse :D
 
Le principe de la jointure externe (outer join) c'est de récupérer tous les éléments d'une table, associés aux éléments d'une seconde table, que la jointure soit vraie ou non.
Exemple : Je veux la liste de tous les clients, ainsi que les numéros de commande s'ils en ont.
 

Code :
  1. SELECT cli.nom, cde.numero, cde.etat
  2. FROM client cli
  3. LEFT OUTER JOIN commande cde ON cde.client_id = cli.id


 
Résultat :


dupont  123456   C
dupont   654321   V
durant   897456   V
dutronc <null>   <null>


 
Maintenant, je veux afiner. En fait, je veux la même chose (tous les clients) mais je n'ai que faire des commandes validée, je ne veux que les commande clôturées.
 
Naïvement, on sera tenté de rajouter dans la clause WHERE un filtre :

Code :
  1. SELECT cli.nom, cde.numero, cde.etat
  2. FROM client cli
  3. LEFT OUTER JOIN commande cde ON cde.client_id = cli.id
  4. WHERE cde.etat = 'C'


 
Pas de chance... Autant V est effectivement différent de C, autant <null> l'est tout autant !


dupont  123456   C


On a un peu perdu l'intérêt de faire la jointure ouverte.
 
On se rend compte en fait que ce filtre sur l'état de la commande fait partie non pas du filtre des résultats, mais du critère de jointure entre la table physique "client" et la table logique "commandes clôtuées".
Du coup, voici la bonne syntaxe :

Code :
  1. SELECT cli.nom, cde.numero, cde.etat
  2. FROM client cli
  3. LEFT OUTER JOIN commande cde ON cde.client_id = cli.id AND cde.etat = 'C'


Résultat :


dupont  123456   C
durant   <null>   <null>
dutronc <null>   <null>


Ca correspond exactement à ce qu'on voulait.
 
Maintenant, toi, ce que tu veux, c'est pas vraiment tous les clients, mais tous les clients qui ne répondent pas à la jointure.
Dans notre cas, c'est durant et dutronc.
Là, on se rend compte que si la jointure est bonne, c'est le filtre qui n'est pas bon : on ne veut garder que ceux qui ont cde.numero à null.
 
Ce qui donne donc :

Code :
  1. SELECT cli.nom, cde.numero, cde.etat
  2. FROM client cli
  3. LEFT OUTER JOIN commande cde ON cde.client_id = cli.id AND cde.etat = 'C'
  4. WHERE cde.numero IS NULL


Résultat :


durant   <null>   <null>
dutronc <null>   <null>


Message édité par MagicBuzz le 03-07-2007 à 21:04:02
Reply

Marsh Posté le 04-07-2007 à 08:58:49    

Tu viens de sauver ma journer MagicBuzz :)
 
Y 2/3 ans, quand j'étais encore à l'IUT, on se bouffait 2h par semaine de SGBD, avec des longues listes de requêtes à faire dans MySQL. Forcément, après, on connaît tout, mais quand tu ne l'utilises plus pendant un moment.... ça sort tout seul.
 
Merci encore

Reply

Marsh Posté le 04-07-2007 à 09:45:44    

Ben si ça peut te rassurer, j'ai appris ce type de jointures à l'IUT aussi (mais avec OpenIngre -prédécesseur de PostGreSQL- et non MySQL c'd'la merde :o)...
Mais il m'aura fallu 5 ans d'utilisation avant de piger comment marche ce truc ;) (forcément, quand on utilise la notation raccourcie, ça saute moins aux yeux...)

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed