Discussion :

[SergioMaster]

J'ai deux tables (simplifiées)

MATIERES
CODE_MATIERE
LIBELLE

FOURNIPAR
CODE_MATIERE
CODE_FOURNISSEUR
PRINCIPAL (O/N)

relation : 1 / (0,n)

je voudrais obtenir une liste des matières avec leur premier fournisseur , de préférences le principal (il devrait y en a toujours un et un seul mais ce serait trop beau !!)

un jeu d'essai

MATIERES
1,NUBUCK
2,VEAU
3,ALCANTARA
4,ETIQUETTE

FOURNIPAR
1,0001,O
1,0002,N
2,0002,O
3,0003,N //<-- le fournisseur principal a disparu, ou il n'y en a jamais eu

Je voudrais obtenir

CODE_MATIERE,LIBELLE,FOURNISSEUR
1,NUBUCK,0001
2,VEAU,0002
3,ALCANTARA,0003
4,ETIQUETTE,null

Comment faire ? je sèche

[SergioMaster]

Après bien des erreurs de syntaxe j'ai trouvé une solution mais la première partie (la CTE) ne pourrait-elle pas être améliorée ? voir remaniée

Code :

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

1
2
3
4
5
6
7
 
WITH F AS (SELECT R.MATIERE,(SELECT FIRST 1 M.CODE_FOURNISSEUR FROM FOURNIPAR M WHERE M.MATIERE=R.MATIERE ORDER BY M.MATIERE,M.PRINCIPAL DESC) AS FOURNISSEUR
FROM MATFRN R
)
 
SELECT M.CODE_MATIERE,M.LIBELLE,F.CODE_FOURNISSEUR
FROM MATIERES M  JOIN F ON N.MATIERE=F.MATIERE

[makowski]

si tu es certain qu'il n'y a qu'un fournisseur principal :

Code :

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

1
2
3
4
 
SELECT M.CODE_MATIERE,M.LIBELLE,F.CODE_FOURNISSEUR
 FROM MATIERES M 
 LEFT JOIN FOURNIPAR F ON F.CODE_MATIERE=M.CODE_MATIERE AND F.PRINCIPAL='O'

s'il peut y en avoir plusieurs :

Code :

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

1
2
3
4
SELECT M.CODE_MATIERE,M.LIBELLE,MAX(F.CODE_FOURNISSEUR)
 FROM MATIERES M 
 LEFT JOIN FOURNIPAR F ON F.CODE_MATIERE=M.CODE_MATIERE AND F.PRINCIPAL='O'
 GROUP BY 1,2

ou

Code :

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

1
2
3
4
SELECT M.CODE_MATIERE,M.LIBELLE,MIN(F.CODE_FOURNISSEUR)
 FROM MATIERES M 
 LEFT JOIN FOURNIPAR F ON F.CODE_MATIERE=M.CODE_MATIERE AND F.PRINCIPAL='O'
 GROUP BY 1,2

[SergioMaster]

si tu es certain qu'il n'y a qu'un fournisseur principal

Oui mais non justement , (voir jeu d'essai matiere 3) ,
donc impossible de tester sur la colonne PRINCIPAL , cela aurait été trop simple

[makowski]

ce que je t'ai envoyé fonctionne

ceci dit, ton modèle est mal fait s'il permet plusieurs "principaux"

s'il peut y en avoir plusieurs :

Code :

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

1
2
3
4
SELECT M.CODE_MATIERE,M.LIBELLE,MAX(F.CODE_FOURNISSEUR)
 FROM MATIERES M 
 LEFT JOIN FOURNIPAR F ON F.CODE_MATIERE=M.CODE_MATIERE AND F.PRINCIPAL='O'
 GROUP BY 1,2

ou

Code :

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

1
2
3
4
SELECT M.CODE_MATIERE,M.LIBELLE,MIN(F.CODE_FOURNISSEUR)
 FROM MATIERES M 
 LEFT JOIN FOURNIPAR F ON F.CODE_MATIERE=M.CODE_MATIERE AND F.PRINCIPAL='O'
 GROUP BY 1,2

[SergioMaster]

Ce que tu m'as indiqué fonctionne parfaitement

Mais , ce que je voulais dire c'est qu'il se peut qu'une matière n'ai aucun fournisseur principal
(Soit parce que celui-ci a été supprimé et non remplacé
Soit parce que jamais indiqué cela dépendant par exemple de l'appel d'offre)

donc on peut se trouver dans les situations suivantes
il y a un fournisseur principal
il y a un fournisseur 'principal' et des 'occasionnels'
il n'y a pas de fournisseur principal , que des occasionnels
il n'y a aucun fournisseur
le cas : il y a deux fournisseurs principaux n'est "encore" jamais arrivé (il y a une contrainte)

[Note] le schéma est simplifié a l'extrême mais c'est a peu près ça

[Cl@udius]

Salut Sergio

ET avec des unions, qu'est-ce que ça donne ?

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
-- Founisseurs principaux
SELECT
  M.CODE_MATIERE, M.LIBELLE, F.CODE_FOURNISSEUR, F.PRINCIPAL
FROM
  MATIERES M
  INNER JOIN FOURNIPAR F
  ON M.CODE_MATIERE = F.CODE_MATIERE
WHERE
  F.PRINCIPAL = 'O'
 
-- Fournisseurs secondaires
UNION
SELECT
  M.CODE_MATIERE, M.LIBELLE, F.CODE_FOURNISSEUR, F.PRINCIPAL
FROM
  MATIERES M
  INNER JOIN FOURNIPAR F
  ON M.CODE_MATIERE = F.CODE_MATIERE
WHERE
  F.PRINCIPAL = 'N' AND
  NOT EXISTS(
    SELECT
      F2.CODE_MATIERE
    FROM
      FOURNIPAR F2
    WHERE
      F2.CODE_MATIERE = F.CODE_MATIERE AND
      F2.PRINCIPAL = 'O')
 
-- Absence de fournisseur
UNION
SELECT
  M.CODE_MATIERE, M.LIBELLE, F.CODE_FOURNISSEUR, F.PRINCIPAL
FROM
  MATIERES M
  LEFT OUTER JOIN FOURNIPAR F
  ON M.CODE_MATIERE = F.CODE_MATIERE
WHERE
  F.CODE_FOURNISSEUR IS NULL;

@+ Claudius

[makowski]

donc on peut se trouver dans les situations suivantes
il y a un fournisseur principal
il y a un fournisseur 'principal' et des 'occasionnels'
il n'y a pas de fournisseur principal , que des occasionnels
il n'y a aucun fournisseur
le cas : il y a deux fournisseurs principaux n'est "encore" jamais arrivé (il y a une contrainte)

et bien mes requêtes répondent parfaitement à toutes ces situations et renvoi ce que tu disais vouloir obtenir

[SergioMaster]

@Claudius Avé
, oui ça ça pourrait marché avec une petite modification sur l'union des fournisseur secondaires pour n'avoir que le premier (le fameux Group by de makowski mais avc les F.PRINCIPAL='N')

@makowski non je ne crois pas , (je refais un essai) mais dans les proposition que tu me fais seul les fournisseurs principaux apparaissent

AND F.PRINCIPAL='O'

[Edit] je joins une partie des résultats pour t'en faire la preuve

avec cette CTE cela fonctionne (enfin je n'ai pas encore vérifié toutes les matières du fichier REEL , il faut dire qu'il y en a +de 5000 et encore je ne parle pas des coloris <- quand j'ai dit que j'avais simplifié )

Code :

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

1
2
WITH F AS (SELECT R.MATIERE,(SELECT FIRST 1 M.CODE_FOURNISSEUR FROM FOURNIPAR M WHERE M.MATIERE=R.MATIERE ORDER BY M.MATIERE,M.PRINCIPAL DESC) AS FOURNISSEUR
FROM MATFRN R)

c'est juste que je trouve le SELECT dans un SELECT très inélégant
et que certains SGBDR on une fonction pour extraire le 1°tuple , c'est pas pour cela que je changerai d'avis sur Firebird

Reste a découvrir le plus performant

[edit] y a pas photo , sur un jeu d'essai plus conséquent :
avec l'union 0.249s (prepare inclus) 44064 fetches
avec mes premiers essais (le select dans un select) 0.827s avec 684211 fetches

Merci a vous 2 , un mix des diverses propositions donne (enfin) un résultat très satisfaisant

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
 
WITH F AS (-- Founisseurs principaux
SELECT
  CODE_MATIERE,CODE_FOURNISSEUR
FROM
  FOURNIPAR
WHERE
  PRINCIPAL = 'O'
-- Fournisseurs secondaires
UNION
SELECT
 F1.CODE_MATIERE,MIN(F1.CODE_FOURNISSEUR)
FROM
  FOURNIPAR F1
WHERE
  PRINCIPAL = 'N' AND
  NOT EXISTS(
    SELECT
      F2.CODE_MATIERE
    FROM
      FOURNIPAR F2
    WHERE
      F2.CODE_MATIERE = F1.CODE_MATIERE AND
      F2.PRINCIPAL = 'O')
GROUP BY 1
)
 
SELECT DISTINCT M.CODE_MATIERE,F.CODE_FOURNISSEUR
FROM MATIERES M LEFT JOIN F ON M.CODE_MATIERE=F.CODE_MATIERE

[makowski]

@makowski non je ne crois pas , (je refais un essai) mais dans les proposition que tu me fais seul les fournisseurs principaux apparaissent

NON, clairement NON

n'oublie pas que j'ai mis un LEFT JOIN

[SergioMaster]

NON, clairement NON
n'oublie pas que j'ai mis un LEFT JOIN

on est bien d'accord , et je n'obtiens que des couples matières,libelle/fournisseurs principal ou matières,libelle/null
dans ce cas

[makowski]

oui,

mais tu peux aussi essayer :

Code :

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

1
2
3
4
5
6
SELECT M.CODE_MATIERE,M.LIBELLE,
COALESCE(MIN(F.CODE_FOURNISSEUR),
(SELECT CODE_FOURNISSEUR FROM FOURNIPAR WHERE CODE_MATIERE=M.CODE_MATIERE AND PRINCIPAL='N'))
 FROM MATIERES M 
 LEFT JOIN FOURNIPAR F ON F.CODE_MATIERE=M.CODE_MATIERE AND F.PRINCIPAL='O'
 GROUP BY 1,2