Dans 2 colonnes, afficher la même colonne selon deux conditions différentes

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.

Pièce jointe 279416

Merci d'avance, bonne soirée !

Compufan

Bonjour Compufan,

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

Grosso modo :

Code:

---

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'

---

Citation:

---

Envoyé par jcdentons

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

---

Non, car Compufan a précisé :

Citation:

---

Envoyé par Compufan

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 ? :weird:

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

Citation:

---

Envoyé par escartefigue

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 ? :weird:

---

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.

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.

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:

---

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... :?

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

Quelque chose comme ça :

Code:

---

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

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...

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

Tatayo.

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

Citation:

---

Envoyé par tatayo

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 :
Pièce jointe 279567

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

Merci du conseil M. Escartefigue ;)

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:

---

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 :
Pièce jointe 279571

Quel est le résultat de la requête

Code:

---

1 2 3

SELECT * FROM Ouvrage_Partenaire_Editeur WHERE O_ID =1

---

Et à quoi sert la colonne O_id_ped_id dans cette table :weird:

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:

---

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:

---

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.

@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 : Pièce jointe 279588

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.

Citation:

---

Envoyé par 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.

---

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 +

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

Citation:

---

Envoyé par Compufan

@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 +

Citation:

---

Envoyé par Compufan

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.

---

D'après ce que je vois, votre fichier ne comporte pas les identifiants mais uniquement les libéllés, du coup je suppose que vous attribuez un nouvel identifiant dans la table cible a chaque occurrence d'un même nom d'éditeur dans le fichier, c'est ballot :aie:
Méthode d'extraction et de chargement à revoir ;)

Citation:

---

Envoyé par SQLpro

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

---

Oui, mais... si je prends mon exemple, à l'époque du choix la contrainte était simple: "ce que tu veux comme SGBD, mais gratuit, et sous Windows.". Fin de la discussion. Et toujours à l'époque, il n'y avait que MaxDb et MySQL. SqlServer était payant, et PostgreSQL uniquement sous Linux.
Entre deux maux, choisit le moindre, j'ai rejeté MySQL...
Les choses ont bien changé, et la bascule sur SqlServeur est très sérieusement envisagée (surtout qu'on a les licences maintenant !).
Il ne nous manque qu'une seule chose: le temps.
Si un changement de SGBD était aussi simple qu'un changement de chemise, ce serait fait depuis longtemps.

Désolé pour l'aparté.

Tatayo.

Citation:

---

Envoyé par tatayo

Oui, mais... si je prends mon exemple, à l'époque du choix la contrainte était simple: "ce que tu veux comme SGBD, mais gratuit, et sous Windows.". Fin de la discussion.

---

Quelle époque ? Parce que en 1997 il y avait MSDE l’ancêtre de SQL Server Express qui était une version gratuite ! Il y avait aussi PostGreSQL 7 qui pouvait fonctionner dans Windows sous CygWin (j'ai eu un client qui fonctionnait comme ça...) et en 2005 nativement sous WIndows.... Sans parler de Ingres de Computer Associates tombé en libre en 2004

Citation:

---

Et toujours à l'époque, il n'y avait que MaxDb et MySQL. SqlServer était payant, et PostgreSQL uniquement sous Linux.

---

Ton projet date donc de plus de 20 ans ? Et aucune remise en cause ???

Citation:

---

Entre deux maux, choisit le moindre, j'ai rejeté MySQL...
Les choses ont bien changé, et la bascule sur SqlServeur est très sérieusement envisagée (surtout qu'on a les licences maintenant !).
Il ne nous manque qu'une seule chose: le temps.
Si un changement de SGBD était aussi simple qu'un changement de chemise, ce serait fait depuis longtemps.

---

Et bien essaye, je pense que tu seras supris !

Citation:

---

Désolé pour l'aparté.

Tatayo.

---

A +

Avant de me tirer dessus à boulet rouge, je n'étais pas décisionnaire à l'époque, j'avais eut du mal à évincer MySQL...
Le projet date maintenant d'une quinzaine d'années, j'avoue que je ne sais plus pourquoi SqlServer avait été écarté (peut-être les limitations de la version gratuite à l'époque...).
PostgreSQL ne tournait pas nativement (CygWin) => écarté.

MaxDB fait parfaitement son travail jusqu'à présent, mais comme nous avons désormais les licences SqlServer, la question de la migration revient de temps en temps.
Mais comme il existe à peu près autant de dialectes SQL que de moteurs, il faut bien vérifier s'il n'y a pas de problème avec les requêtes. IfNull par exemple n'existe pas avec SqlServer (je sais, les NULL c'est le mal !).
Il faut aussi prévoir la migration des 200Go de données d'une base utilisée quasiment H24.

Je ne suis pas nombreux (sic) et j'ai déjà un planning chargé jusqu'à la fin de l'année minimum (plus les imprévus).

Et comme cette migration "n'apporte rien" du point de vue utilisateur, elle n'est pas une priorité niveau planning...

On ne fais pas toujours ce qu'on veux...

Tatayo.

Citation:

---

Envoyé par tatayo

Avant de me tirer dessus à boulet rouge, je n'étais pas décisionnaire à l'époque, j'avais eut du mal à évincer MySQL...
Le projet date maintenant d'une quinzaine d'années, j'avoue que je ne sais plus pourquoi SqlServer avait été écarté (peut-être les limitations de la version gratuite à l'époque...).
PostgreSQL ne tournait pas nativement (CygWin) => écarté.

MaxDB fait parfaitement son travail jusqu'à présent, mais comme nous avons désormais les licences SqlServer, la question de la migration revient de temps en temps.
Mais comme il existe à peu près autant de dialectes SQL que de moteurs, il faut bien vérifier s'il n'y a pas de problème avec les requêtes. IfNull par exemple n'existe pas avec SqlServer (je sais, les NULL c'est le mal !).

---

1) vous n'avez pas étudier le niveau du SQL ni sa conformité au standard... C'est stupide !
Pour SQL Server vous avez le choix : ISNULL, NULLIF ou encore, créer une UDF appelée IFNULL !

Citation:

---

Il faut aussi prévoir la migration des 200Go de données d'une base utilisée quasiment H24.

---

Pour info, avec SSIS (intégré avec SQL Server) 1 To = 30mn.
https://technet.microsoft.com/en-us/...=sql.100).aspx
Il faut maquetter et planifier... vous ne devriez pas mettre plus d'une heure à faire cette migration...

Citation:

---

Je ne suis pas nombreux (sic) et j'ai déjà un planning chargé jusqu'à la fin de l'année minimum (plus les imprévus).

Et comme cette migration "n'apporte rien" du point de vue utilisateur, elle n'est pas une priorité niveau planning...

---

Jusqu'au jour ou vous aurez un crash majeur et perdrez toutes les données, car je suppose qu'il n'y a pas de système de haute dispo intégré à MaxDB...

Citation:

---

On ne fais pas toujours ce qu'on veux...

Tatayo.

---

A +