Discussion :

[coyote90]

Bonjour,

soit trois tables :
personne
p_id
p_nom

competence_personne
c_id
p_id

competence
c_id
c_libelle


Comment écrire la requête qui permettrait d'avoir toutes les personnes ayant les compétences 1 + 5 + 10 etc...

merci d'avance

[litllechicken]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
select * 
from personne,competance_personne c1,competance_personne c2 ,competance_personne c3
where personne.p_id = c1.p_id
AND personne.p_id = c2.p_id
AND personne.p_id = c3.p_id
AND c1.c_id = 1
AND c2.p_id = 5
AND c3.p_id = 10

[coyote90]

merci, si je comprend bien, je dois créer autant d'alias que de compétence recherchée...

[litllechicken]

ou (pour éviter les alias ^^):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
 
select * from personne where 
p_id = (select p_id from competance_personne where c_id=1)
AND p_id = (select p_id from competance_personne where c_id=5)
AND p_id = (select p_id from competance_personne where c_id=10)

il doit même exister mieux !!!

[coyote90]

J'espère qu'il y a mieux,
là les perfs sont catastrophiques !!!

merci pour l'aide

[litllechicken]

la seconde solution est moins lourde ^^

[ced]

Bonjour,

Alors, à part faire autant de jointures que tu as de conditions d'égalités à tester, ça n'est pas évident.
Tu peux essayer de passer par la fonction GROUP_CONCAT de MySQL, qui permet de grouper, avec un séparateur, toutes les occurences d'un même groupe dans une seule chaîne de caractères.
Il faut ensuite rechercher, dans cette chaîne, les valeurs 1, 5 et 10, avec la fonction INSTR. Seul problème, c'est que 10 contient 1. Donc une personne qui aurait 5 et 10 peut sortir au test... alors qu'elle n'a pas la compétence 1...
Donc, il faut encore ruser un coup de plus, pour concaténer non pas seulement le numéro de la compétence, mais ce numéro, plus un autre caractère, qui permettra de distinguer 1 et 10 dans l'analyse d'une chaîne de caractères.

Bref, ça ressemble à une énorme bidouille , mais ça fonctionne avec la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT p.p_nom, GROUP_CONCAT(CONCAT(cp.c_id, '.') ORDER BY cp.c_id SEPARATOR ',')
FROM personne p
INNER JOIN competence_personne cp ON p.p_id = cp.p_id
GROUP BY p.p_nom
HAVING INSTR(GROUP_CONCAT(CONCAT(cp.c_id, '.') ORDER BY cp.c_id separator ','), '1.') > 0
AND INSTR(GROUP_CONCAT(CONCAT(cp.c_id, '.') ORDER BY cp.c_id SEPARATOR ','), '5.') > 0
AND INSTR(GROUP_CONCAT(CONCAT(cp.c_id, '.') ORDER BY cp.c_id SEPARATOR ','), '10.') > 0

Ne reste plus alors, pour tester une compétence en plus (par exemple la 6) qu'à l'ajouter dans le HAVING sans oublier le "." derrière le 6 .

OK, je reconnais que c'est tiré par les cheveux, mais ça marche et pour le coup, il ne doit pas y avoir des perfs dégradées (y'a pas une sous-requête, dans ma bidouille ).

ced

PS : personnellement, je préfère la solution avec les jointures, mais écrites à la mode SQL 92, pas SQL 86, parce que c'est plus lisible et plus facile à faire évoluer (pour ajouter une compétence, on copie une condition de jointure, on la colle et on l'adapte) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT p.p_nom
FROM personne p
INNER JOIN competence_personne cp1 ON p.p_id = cp1.p_id AND cp1.c_id = 1
INNER JOIN competence_personne cp5 ON p.p_id = cp5.p_id AND cp5.c_id = 5
INNER JOIN competence_personne cp10 ON p.p_id = cp10.p_id AND cp10.c_id = 10

C'est quand même moins crade que ma belle bidouille, non ?