Discussion :

[Slylord]

Bonjour à tous,

je rencontre des difficultés à écrire une requête, et après de nombreuses recherches sans trouver de solution sur internet, je viens vous soumettre le problème.

Je dispose de deux tables (modélisant chacune une relation entre deux entités, i.e. du "manytomany"):
- une table RELATION1 associant des codes à des compétences: un CODE renvoie vers 1..N COMPÉTENCES
- une table RELATION2 associant des socles à des compétences: un SOCLE est composé de 1..N COMPÉTENCES



Ex de données RELATION1:

Code :

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

1
2
3
4
5
6
code1, competence1
code1, competence2
code1, competence3
code2, competence1
code2, competence3
code2, competence4

Ex de données RELATION2:

Code :

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

1
2
3
4
5
6
7
socle1, competence1
socle1, competence2
socle1, competence3
socle1, competence4
socle2, competence1
socle2, competence2
socle2, competence5

Je cherche à déterminer, pour un SOCLE donné, la liste des CODES qui possèdent toutes les COMPETENCES du socle.

Dans l'exemple ci-dessus:
- pour "socle1" passé en paramètre, je souhaite "code1" et "code2" car les deux codes ont toutes leurs compétences faisant partie des compétences de "socle1"
- pour "socle2" passé en paramètre, il n'y a aucun retour attendu car aucun code ne contient "competence5".

J'espère être clair.

Merci d'avance

[al1_24]

Ce problème doit pouvoir se résoudre par la division relationnelle...

[Slylord]

J'étais passé à côté, enfin un début de piste, merci

[fsmrel]

Bonjour Slylord


Dans le style de Chris Date, au début des années quatre-vingts. Pour ce qui concerne le socle socle1 :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
select distinct id_code
from relation1 as dividende
where not exists
     (select id_socle, id_competence
      from (select distinct id_socle, id_competence from relation2 where id_socle = 'socle1') as diviseur 
      where not exists
           (select *
            from  relation1 as inter
            where inter.id_code = dividende.id_code
            and   inter.id_competence = diviseur.id_competence)) ;

[fsmrel]

Quelques explications au sujet de la requête que j’ai proposée :


Comme le suggère al1_24, la division relationnelle est faite pour résoudre votre problème, grâce à l’opérateur DIVIDEBY :

(Relation1 DIVIDEBY (Relation2 WHERE id_socle = 'socle1')) {id_code}

Ce qui se lit :

Diviser Relation1 par la restriction de Relation2 à 'socle1', puis projeter le résultat sur id_code.

SQL ne proposant pas cet opérateur, il faut donc le paraphraser en faisant des contorsions. Prenons donc l’énoncé :

(1) « Quels sont les codes qui comportent toutes les références du socle 'socle1' »

La quantification universelle permet de résoudre le problème. Mais, si SQL propose un avatar de la quantification existentielle avec EXISTS, ce langage ne propose rien quant à la quantification universelle. Heureusement, on sait exprimer la quantification universelle au moyen de la quantification existentielle :

(∀x)Fx ≡ —(∃x)–Fx

Du fait de la double négation, on paraphrase donc ainsi l’énoncé (1) :

(2) « Quels sont les codes pour lesquels il n’existe pas de compétence qui n’existe pas pour le socle ' socle1' »

D’où la traduction un peu tarabiscotée que j'avais faite en SQL.


Il existe d’autres façons de s’en sortir (au moyen de la comparaison relationnelle par exemple, proposée par Date), mais celle que j’ai proposée remonte aux années soixante-dix, proposée par Date aux utilisateurs de SEQUEL (SQL), et qui s’appuie en l'occurrence sur la logique de Frege.

[Waldar]

J'aime bien la version agrégée de cette solution.
Requêtes faites avec SQL-Server 2012 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
create table RELATION1
( code       varchar(8)
, competence varchar(20)
, constraint pk_relation1
    primary key (code, competence)
);
 
create table RELATION2
( socle      varchar(8)
, competence varchar(20)
, constraint pk_relation2
    primary key (socle, competence)
);
 
insert into RELATION1 values
('code1', 'competence1'),
('code1', 'competence2'),
('code1', 'competence3'),
('code1', 'competence4'),
('code2', 'competence1'),
('code2', 'competence3'),
('code2', 'competence4');
 
insert into RELATION2 values
('socle1', 'competence1'),
('socle1', 'competence2'),
('socle1', 'competence3'),
('socle1', 'competence4'),
('socle2', 'competence1'),
('socle2', 'competence2'),
('socle2', 'competence5');
 
 
  select code
    from RELATION1
   where competence in (select competence from RELATION2 where socle = 'socle1')
group by code
  having count(*) = (select count(*) from RELATION2 where socle = 'socle1');
 
CODE
-----
code1

@fsmrel, je pense qu'on peut légèrement simplifier votre requête.
La présence d'une clef primaire dans RELATION2 permet de supprimer le DISTINCT de la ligne #5, et dès lors cette vue en ligne n'a plus de sens :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT DISTINCT id_code
  FROM relation1 AS dividende
 WHERE NOT EXISTS (SELECT NULL
                     FROM relation2 AS diviseur 
                    WHERE diviseur.id_socle = 'socle1'
                      AND NOT EXISTS (SELECT NULL
                                        FROM relation1 AS inter
                                       WHERE inter.id_code       = dividende.id_code
                                         AND inter.id_competence = diviseur.id_competence));

[Slylord]

Merci beaucoup pour votre aide Je vais vérifier et si ok, je tag résolu. Merci encore !