SQL Requête difficile sur BDD

Version imprimable

24/09/2011, 13h05
rkade

SQL Requête difficile sur BDD
Bonjour à tous,

je dois réaliser des requêtes sur une base de données oracle express.

Cependant, je sèche complètement sur une question.

Voici ma base de données :

DRAGONS :
- dragon (représente le nom)
- comportement_amoureux
REPAS:
- dragon_aimant (représente le nom du dragon qui aime)
- dragon_aime (représente le nom du dragon qui est aimé)
La requête doit retourner le nom des dragons timides ayant des relations avec tous les dragons macho. (Ayant des relations = est aimé ou aime)

Bien évidemment nous voyons en relationnel qu'il s'agit d'une division. J'avais penser à du count( ) dans la requête mais je ne vois pas du tout comment m'y prendre.

Je ne vous demande pas forcement de me donner la solution, mais des petites indications ?

Merci à tous
24/09/2011, 13h40
CinePhil

Indice qui devrait t'aider à construire ta requête :

Citation:

Envoyé par rkade

La requête doit retourner le nom des dragons timides ayant des relations avec tous les dragons macho. (Ayant des relations = est aimé ou aime)

Autrement dit :
Les dragons timides dont le nombre de relations avec des dragons machos est égal au nombre de dragons machos.

Il faut effectivement COUNT et GROUP BY ainsi que HAVING.

Il y a des exemples dans le forum mais je ne saurais pas trop te dire sur quels critères les chercher. Peut-être avec ces trois mots du langage SQL que je viens de citer ?

Oui, j'étais parti sur les mêmes idées.

Pour ce qui est du nombre de dragon macho, j'ai la requête :
Code:

1 2 3 select count(*) from dragons where comportement_amoureux='macho'
Mais pour la partie : "les dragons timides dont le nombre de relations avec des dragons macho" ... J'ai du mal.

Ce que je souhaiterai, c'est savoir comment je peux lister les dragons avec qui les dragons timide ont des relations.

Ensuite, je mets un group by dragon, et ensuite un having sur le count.

j'avais pensé à un truc du genre :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT d1.dragon, count(*) as nb_dragons_related FROM (dragons d1 JOIN aime ON d1.dragon=dragon_aimant) JOIN dragons d2 ON dragon_aime = d2.dragon WHERE d1.comportement_amoureux='timide' AND d2.comportement_amoureux='macho' GROUP BY d1.dragon UNION ( SELECT d1.dragon, count(*) as nb_dragons_related FROM (dragons d1 JOIN aime ON d1.dragon=dragon_aimant) JOIN dragons d2 ON dragon_aime = d2.dragon WHERE d1.comportement_amoureux='macho' AND d2.comportement_amoureux='timide' GROUP BY d1.dragon )
mais malheureusement, ce n'est pas ça :aie:

EDIT : En fait j'ai peut-être trouvé la bonne requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 select * from ( SELECT d1.dragon, count(*) as nb_dragons_related FROM (dragons d1 JOIN aime ON d1.dragon=dragon_aimant) JOIN dragons d2 ON dragon_aime = d2.dragon WHERE d1.comportement_amoureux='timide' AND d2.comportement_amoureux='macho' GROUP BY d1.dragon UNION ( SELECT d1.dragon, count(*) as nb_dragons_related FROM (dragons d1 JOIN aime ON d1.dragon=dragon_aimant) JOIN dragons d2 ON dragon_aime = d2.dragon WHERE d1.comportement_amoureux='macho' AND d2.comportement_amoureux='timide' GROUP BY d1.dragon ) ) where nb_dragons_related = (select count(*) from dragons where comportement_amoureux='macho')
Dites moi ce que vous en pensez ? Si elle vous paraît exacte ?

Reprenons ce que j'ai dit tout à l'heure :

Citation:

Les dragons timides dont le nombre de relations avec des dragons machos est égal au nombre de dragons machos.

1) Les dragons timides qui aiment des dragons machos :

Code:

1
2
3
4
5
6
SELECT d.dragon
FROM DRAGONS d
INNER JOIN aime a ON a.dragon_aimant = d.dragon
    INNER JOIN dragon d1 ON d1.dragon = a.dragon_aime
WHERE d.comportement_amoureux = 'timide'
    AND d1.comportement_amoureux = 'macho'

2) On ajoute à la première requête les dragons timides qui sont aimés des dragons machos :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT d.dragon AS dragon_timide, 
    d1.dragon AS autre_dragon
FROM DRAGONS d
INNER JOIN aime a ON a.dragon_aimant = d.dragon
    INNER JOIN dragon d1 ON d1.dragon = a.dragon_aime
WHERE d.comportement_amoureux = 'timide'
    AND d1.comportement_amoureux = 'macho'
UNION
SELECT d.dragon, d1.dragon
FROM DRAGONS d
INNER JOIN aime a ON a.dragon_aime = d.dragon
    INNER JOIN dragon d1 ON d1.dragon = a.dragon_aimant
WHERE d.comportement_amoureux = 'timide'
    AND d1.comportement_amoureux = 'macho'

Note : Si une relation est réciproque (timide X aime macho Y et timide X est aimé de macho Y), UNION ne donnera qu'une fois le couple.

3) On ne retient que les dragons timides qui ont autant de relations que le nombre de dragons machos :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT tmp.dragon_timide
FROM
(
    SELECT d.dragon AS dragon_timide, 
        d1.dragon AS autre_dragon
    FROM DRAGONS d
    INNER JOIN aime a ON a.dragon_aimant = d.dragon
        INNER JOIN dragon d1 ON d1.dragon = a.dragon_aime
    WHERE d.comportement_amoureux = 'timide'
        AND d1.comportement_amoureux = 'macho'
    UNION
    SELECT d.dragon, d1.dragon
    FROM DRAGONS d
    INNER JOIN aime a ON a.dragon_aime = d.dragon
        INNER JOIN dragon d1 ON d1.dragon = a.dragon_aimant
    WHERE d.comportement_amoureux = 'timide'
        AND d1.comportement_amoureux = 'macho'
) tmp
GROUP BY tmp.dragon_timide
HAVING COUNT(*) = (
    SELECT COUNT(*)
    FROM DRAGONS
    WHERE comportement_amoureux = 'macho'
)

24/09/2011, 19h43
estofilo

La requête UNION ne serait pas un peu lourdingue par rapport au besoin?
Il paraitrait plus naturel et plus simple de faire un OR dans la condition de jointure (aimant OR aimé).
25/09/2011, 01h23
rkade

Merci beaucoup à toi CinePhil ! Tes explications sont très clairs.

Pour enlever l'union, et le remplacer par une condition OR. Je ne vois pas comment faire, j'ai réfléchis un peu sans trouver de réponse.
25/09/2011, 09h31
CinePhil

J'avais pensé le faire sans UNION au début mais comme c'est une condition double et sur deux instances de la table dragons, je n'ai pas trouvé comment faire (sans chercher des heures non plus).

Avec le OR, je voyais une condition de ce style:

Code:

1
2
3
4
5
6
SELECT distinct d.dragon as timide
FROM DRAGONS d
INNER JOIN aime a ON (a.dragon_aime = d.dragon OR a.dragon_aimant=d.dragon)
INNER JOIN dragons d1 ON (d1.dragon = a.dragon_aimant OR d1.dragon = a.dragon_aime)
WHERE d.comportement_amoureux = 'timide'
 AND d1.comportement_amoureux = 'macho';

27/09/2011, 19h22
rkade

Je ne comprends pas la requête.

Car la jointure va se faire de quelle manière ? Comment le OR dans le inner join va fonctionner ?
27/09/2011, 19h57
estofilo

Conceptuellement, le "OU" vient de l'énoncé qui dit que "être en relation c'est est aimé ou aime", c.a.d que le 1er dragon doit être dans la colonne dragon_aimant ou dans la colonne dragon_aime, sachant que l'autre dragon doit figurer dans l'autre colonne.

Le souci avec cette formulation vient peut-être du fait que tu n'as pratiqué jusqu'ici que du JOIN ou le ON serait limité à une équijointure (c.a.d une colonne de la table de gauche=une autre colonne de la table droite), mais en réalité on peut mettre n'importe quelle condition dans la clause ON. Quand la condition est vraie, l'exécuteur garde les lignes et quand elle est fausse il les élimine, peu importe la complexité de la condition.
28/09/2011, 08h47
rkade

En fait on a vu les jointures avec des conditions mais que en relationnel. En SQL, tu as raison, c'est la première fois que je vois ça.

Merci, c'est vrai que ta requête est beaucoup plus courte, et je la trouve très compréhensible maintenant.

Bonne journée à tous