Discussion :

[Compufan]

Bonjour, je poste un sujet pour avoir une aide sur une requête que je n'arrive pas à construire. Je développe une application de gestion de bibliothèque et pour cela j'ai une requête à effectuer qui donne, pour un ouvrage donné, son éditeur et son préfacier. Pour des soucis de factorisation/non-redondance, les éditeurs et préfaciers sont stockés au sein d'une même table générique, appelée "Partenaire_Editeur". La table "Type_Partenaire_Editeur" permet quant à elle de savoir quel est le type (préfacier ou éditeur).
Les ouvrages peuvent avoir des préfaciers/éditeurs en commun avec d'autres ouvrages.

Pour le moment je souhaiterais avoir un résultat de ce style :

Partenaire_Editeur.p_nom AS Nom_Editeur | Partenaire_Editeur.p_nom AS Nom_Prefacier
--------------------------------------|---------------------------------------
AAAA BBBB
AAAA NULL
CCCC EEEE
NULL EEEE

Le casse-tête consiste donc à prendre l'éditeur et le préfacier correspondant à un ouvrage et de les séparer dans 2 colonnes différentes pour la requête... alors qu'ils proviennent de la même colonne : Partenaire_Editeur.p_nom. On admet qu'il existe au plus une seule relation entre un Ouvrage et un Partenaire_Editeur de type Editeur, et au plus une seule relation entre un Ouvrage et un Partenaire_Editeur de type Préfacier.
Toutes les configurations sont possibles et avoir un éditeur comme un préfacier est facultatif. A chaque ligne correspond un ouvrage. L'image ci-dessous résume le MPD permettant de lier les infos.



Merci d'avance, bonne soirée !

Compufan

[jcdentons]

Bonjour Compufan,

Il suffit de faire deux jointures internes sur la même table.

Grosso modo :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT Editeur.ped_nom AS NOM_EDITEUR, Partenaire.ped_nom AS NOM_PARTENAIRE
FROM Ouvrage
INNER JOIN Ouvrage_Partenaire_Editeur as LienOuvragePartenaire ON Ouvrage.o_id = LienOuvragePartenaire.O_id
INNER JOIN Partenaire_Editeur as Partenaire ON LienOuvragePartenaire.Ped_id = Partenaire.ped_id
INNER JOIN Type_Partenaire_Editeur as TypePartenaire ON Partenaire.Tped_id = TypePartenaire.tped_id
INNER JOIN Ouvrage_Partenaire_Editeur as LienOuvrageEditeur ON Ouvrage.o_id = LienOuvrageEditeur.O_id
INNER JOIN Partenaire_Editeur as Editeur ON LienOuvrageEditeur.Ped_id = Editeur.ped_id
INNER JOIN Type_Partenaire_Editeur as TypeEditeur ON Editeur.Tped_id = TypeEditeur.tped_id
WHERE TypeEditeur.tped_nom = 'EDITEUR'
AND TypePartenaire.tped_nom = 'PARTENAIRE'

[escartefigue]

Il suffit de faire deux jointures internes sur la même table.

Non, car Compufan a précisé :

Toutes les configurations sont possibles et avoir un éditeur comme un préfacier est facultatif. A chaque ligne correspond un ouvrage. L'image ci-dessous résume le MPD permettant de lier les infos.

Il faut donc faire des jointures de type FULL OUTER JOINpour tenir compte de cette règle
Règle que je trouve étrange d'ailleurs : comment un ouvrage peut il ne pas avoir d'éditeur ?

[Compufan]

Certains ouvrages de ma base datent du Xiii ème siècle. L'éditeur n'est du coup pas forcément connu. 😉

[tatayo]

Non, car Compufan a précisé :

Il faut donc faire des jointures de type FULL OUTER JOINpour tenir compte de cette règle
Règle que je trouve étrange d'ailleurs : comment un ouvrage peut il ne pas avoir d'éditeur ?

Pourquoi des FULL OUTER JOIN ? Des LEFT OUTER JOIN ne suffisent pas ici ?
Je doute qu'il cherche à récupérer des éditeurs sans ouvrage.

Tatayo.

[jcdentons]

Merci escartefigue pour la rectification.
Compufan, je pense qu'à partir de la requête que je vous ai fournie et des commentaires d'escartefigue et tatayo, vous pouvez trouver la solution qui vous convient.

[Compufan]

Après test de la requête, avec des INNER JOIN, seuls les ouvrages ayant un éditeur, ou un préfacier, ou les deux ont été comptabilisés. ~800 résultats sur 4 012 en théorie, ce n'était pas très satisfaisant.

J'ai essayé avec la requête suivante afin d'avoir les 4012 ouvrages :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT Ouvrage.o_titre, Editeur.ped_nom AS NOM_EDITEUR, Partenaire.ped_nom AS NOM_PARTENAIRE
FROM Ouvrage
	LEFT OUTER JOIN Ouvrage_Partenaire_Editeur AS LienOuvragePartenaire ON Ouvrage.o_id = LienOuvragePartenaire.o_id
	LEFT OUTER JOIN Partenaire_Editeur AS Partenaire ON LienOuvragePartenaire.ped_id = Partenaire.ped_id
	LEFT OUTER JOIN Type_Partenaire_Editeur AS TypePartenaire ON Partenaire.Tped_id = TypePartenaire.Tped_id
 
	LEFT OUTER JOIN Ouvrage_Partenaire_Editeur AS LienOuvrageEditeur ON Ouvrage.o_id = LienOuvrageEditeur.o_id
	LEFT OUTER JOIN Partenaire_Editeur AS Editeur ON LienOuvrageEditeur.ped_id = Editeur.ped_id
	LEFT OUTER JOIN Type_Partenaire_Editeur AS TypeEditeur ON Editeur.Tped_id = TypeEditeur.Tped_id
WHERE ((TypeEditeur.tped_nom = 'Editeur' OR LienOuvrageEditeur.o_id IS NULL OR LienOuvrageEditeur.ped_id IS NULL)
AND (TypePartenaire.tped_nom = 'Préfacier' OR LienOuvragePartenaire.o_id IS NULL OR LienOuvragePartenaire.ped_id IS NULL))

Il me manque toujours les ouvrages dont on ne connaît ni l'auteur, ni le préfacier. Un SELECT COUNT(*) sur Ouvrage me confirme bien qu'il y a 4012 lignes, mais ma requête ne m'en renvoie que 3917. Je ne saisis pas où est le hic étant donné que si il y absence d'information sur LienOuvrageEditeur/Partenaire (IS NULL) c'est que l'Ouvrage n'a pas d'éditeur/préfacier connu à ce jour, du coup ma condition de filtrage devrait laisser passer chacun des ouvrages puisque je fais une union des différents cas possibles...

[escartefigue]

Le filtre sur le type éditeur/préfacier ne doit pas être dans la clause WHERE, mais dans votre prédicat de jointure
De plus, les "OR" que vous avez mis en cascade rendent la requete "non sargable" elle risque de ramer fortement si le volume dans les tables est significatif !
Enfin, seules les jointures enter ouvrage et la table de liens doit etre de type OUTER, les autres jointures doivent être de type INNER (sauf si votre database à des problèmes d'intégrité des données !)
en effet : tout éditeur dans la table des liens trouve forcément correspondance dans la table des éditeurs, idem pour les types

[escartefigue]

Quelque chose comme ça :

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
SELECT OUV.o_titre
     , PAR1.ped_nom AS NOM_EDITEUR, 
     , PAR2.ped_nom AS NOM_PREFACIER
 
FROM Ouvrage as OUV
-- Recherche de l'éditeur
LEFT  OUTER JOIN Ouvrage_Partenaire_Editeur AS OPE1
 ON   OPE1.id = OUV.ID
INNER JOIN Partenaire_Editeur               AS PAR1 
 ON   PAR1.ped_id = OPE1.ped_id
INNER JOIN Type_Partenaire_Editeur          AS TYP1
 ON   TYP1.Tped_id = PAR1.Tped_id
 AND  TYP1.Tped_nom = 'Editeur'
-- Recherche du préfacier
LEFT  OUTER JOIN Ouvrage_Partenaire_Editeur AS OPE2
 ON   OPE2.id = OUV.ID
INNER JOIN Partenaire_Editeur               AS PAR2
 ON   PAR2.ped_id = OPE2.ped_id
INNER JOIN Type_Partenaire_Editeur          AS TYP2
 ON   TYP2.Tped_id = PAR2.Tped_id
 AND  TYP2.Tped_nom = 'Préfacier'

A noter qu'il eut été préférable d'utiliser des codes types (ex : E pour éditeur et P pour préfacier) plutôt que des libellés

[Compufan]

Toujours une partie des ouvrages qui ne passe pas (celle sans éditeur ni préfacier).

On est bien d'accord que l'INNER JOIN garde l'intersection entre deux tables ? Du coup j'ai l'impression que même si, au départ, tous les ouvrages sont sélectionnés, le reste est ensuite boulotté par les derniers INNER JOIN puisqu'on cherche les Partenaire_Editeur qui ont une correspondance dans Ouvrage_Partenaire_Editeur
Mais de l'autre côté si on met un LEFT OUTER JOIN aux 3 bouts, il y a un nombre de doublons assez incroyable (ce qui dans un sens n'est pas déconnant). Si on en met aux 2 premiers morceaux, le résultat retourné est exactement le même.

Travaillant sous HFSQL (contrainte technique), je ne peux pas disposer d'une fonction telle que NULLIF ou quelque chose dans le genre qui aurait pu m'être utile.

Keep digging

EDIT : je peux peut-être essayer de m'en sortir par un UNION select, en prenant que les ouvrages ayant éditeur et préfacier à NULL... Même si niveau perfos y a des chances que ce soit bourrin...

[tatayo]

Et en n'utilisant que des jointures externes, est-ce que ça fonctionne ?

Tatayo.

[escartefigue]

Procédez par étapes sur un échantillon d'ouvrages, ce sera plus facile de diagnostiquer là où ça coince

Par exemple, ne traitez dans un premier temps que les éditeurs seuls, puis les préfaciers seuls, en filtrant sur une dizaines d'ouvrages

[Compufan]

Et en n'utilisant que des jointures externes, est-ce que ça fonctionne ?

Tatayo.

Avec uniquement des LEFT OUTER JOIN tous les ouvrages sont présents, mais il y a des doublons. Et le SELECT DISTINCT ne les squeeze pas puisque les tuples dans leur globalité restent uniques.
Pour une ligne d'ouvrage on a 4 lignes retournées :


... Alors que cet ouvrage ne comprend qu'un éditeur seul : "Manuscrit".

Merci du conseil M. Escartefigue

[SQLpro]

C'est le type même de colle, sur les jointures, que je pose aux stagiaires d'une formation en SQL avancé chez Orsys...
http://www.orsys.fr/formation-sql-se...ql-avancee.asp

Voici la solution...

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
SELECT Ouvrage.o_titre, Editeur.ped_nom AS NOM_EDITEUR, Partenaire.ped_nom AS NOM_PARTENAIRE
FROM   Ouvrage
 
	    LEFT OUTER JOIN Ouvrage_Partenaire_Editeur AS LienOuvragePartenaire 
          ON Ouvrage.o_id = LienOuvragePartenaire.o_id
	    LEFT OUTER JOIN Partenaire_Editeur AS Partenaire 
	    INNER JOIN Type_Partenaire_Editeur AS TypePartenaire 
          ON Partenaire.Tped_id = TypePartenaire.Tped_id AND TypePartenaire.tped_nom = 'Préfacier'
          ON LienOuvragePartenaire.ped_id = Partenaire.ped_id
 
	    LEFT OUTER JOIN Ouvrage_Partenaire_Editeur AS LienOuvrageEditeur 
          ON Ouvrage.o_id = LienOuvrageEditeur.o_id
	    LEFT OUTER JOIN Partenaire_Editeur AS Editeur 
	    INNER JOIN Type_Partenaire_Editeur AS TypeEditeur 
          ON Editeur.Tped_id = TypeEditeur.Tped_id AND TypeEditeur.tped_nom = 'Editeur'
          ON LienOuvrageEditeur.ped_id = Editeur.ped_id

Il en existe d'autres, à base de parenthèses ou de sous requêtes.

Mais la plupart des développeurs ayant des notions assez vague en mathématique oublient des principes élémentaires et de base des opérateurs et notamment la commutativité...

Si le INNER JOIN est bien commutatif, tout comme le FULL OUTER JOIN, il n'en va pas de même des LEFT et RIGHT outer join.
La position de la clause ON à donc une importance extrême !

A +

Et pour apprendre le SQL, rien ne vaut les ouvrages que j'ai écrit :

[escartefigue]

Quel est le résultat de la requête

Code :

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

1
2
3
SELECT * 
FROM Ouvrage_Partenaire_Editeur 
WHERE O_ID =1

Et à quoi sert la colonne O_id_ped_id dans cette table

[tatayo]

SqlPro: malheureusement cette méthode ne fonctionne pas avec tous les SGBDs... MaxDb la refuse par exemple, et j'ai bien peur que ce soit le cas aussi avec HFSQL.

Code sql :

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

1
2
3
4
5
6
 
select a.*
from article as a
left outer join sku as s
inner join lig_cde_clt_det as l on l.sku_c_code = s.sku_c_code
on s.art_c_ref = a.art_c_ref

Résultat:

Code :

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

1
2
3
4
5
6
7
8
Auto Commit: Off, SQL Mode: Internal, Isolation Level: Committed
 Syntax error or access violation;-5015 POS(59) Missing keyword:ON
select a.*
from article as a
left outer join sku as s
inner join lig_cde_clt_det as l on l.sku_c_code = s.sku_c_code
on s.art_c_ref = a.art_c_ref

Tatayo.

[Compufan]

@EscarteFigue : je travaille en HFSQL avec l'AGL Windev (contrainte technique), du coup lorsque j'ai généré mon MPD et ajouté mes relations il a été automatiquement ajouté.
C'est censé être une clé composée. Je ne m'en sers pas particulièrement.

Voici le résultat :

Logique puisque cet ouvrage comprend à la fois un éditeur et un préfacier, du coup 2 liens vers des partenaires éditeur ont été créés.
Oh wait, faut que je révise mon script d'import. Ca devrait pas mettre 2 liens !

Les données sont ajoutées depuis un fichier .csv, et un script que j'ai créé insère ensuite les valeurs des colonnes en fonction des tables. Voici quelques lignes du fichier (non exhaustif) :

"Le Sermon du siècle";"Guichard de BEAULIEU";"Manuscrit";1200;;"Edition moderne par Achille Jubinal en 1834 éd. Techener et Silvestre "
"L'Enfance d'Ogier le Danois ";"ADENET LE ROI";"Manuscrit";1270;;
"Joie et douleur de Notre-Dame";"Blaise d'AURIOL";;1520;;
"Rymes";"Pernette DU GUILLET";"Jean de Tournes";1545;"Antoine DU MOULIN";"Epitaphes par Maurice Scève et Jean de Vauzelles"

La colonne 3 correspond à l'éditeur, la 5 au préfacier.

[SQLpro]

SqlPro: malheureusement cette méthode ne fonctionne pas avec tous les SGBDs... MaxDb la refuse par exemple, et j'ai bien peur que ce soit le cas aussi avec HFSQL.

Hé bien arrêtez de travailler avec des SGBDR de merde alors qu'il y en a des gratuits qui fonctionnent parfaitement bien....

J'ai mis -1 a compufan parce qu'il n'a pas respecter la charte de postage et que j'en ais marre de bosser pour aider des gens qui ne respectent pas les autres ! Et font perdre du temps à tous.

Au final, il faut tester la solution avec des sous requêtes. Cette solution étant nettement moins performante évidemment ! Parce qu'il faut faire des sous requêtes dans les clauses ON des jointures....


A +

[Compufan]

@Sqlpro : pas le choix me concernant, c'est un projet d'école, en relation avec un client, et Windev/HFSQL est imposé.

[SQLpro]

@Sqlpro : pas le choix me concernant, c'est un projet d'école, en relation avec un client, et Windev/HFSQL est imposé.

C'est pas une raison...
Si vous voulez être un professionnel vous avez aussi une obligation de conseil.
Le choix d'un SGBDR avant étude de l'application est aussi con que d'acheter telle ou telle voiture et se demande APRES si on peut aller sur la route X !

A +