Valeur Min / Valeur Max

Version imprimable

bonjour à tous,
je suis bloqué sur la création de ma requête SQL.

J'ai une table fournisseur, une table produit et une table produit_fournisseur (un founisseur peut avoir plusieurs produit et un produit peut-etre chez plusieurs fournisseur
J'ai la requete suivante qui le liste mes produits par fournisseur
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 select mercuriale.IDMERCURIALE as ID, mercuriale.Designation as LIB, FOURNISSEUR.Raison_Sociale as RS, MERCURIALE_FOURNISSEUR.Prix as Prix from mercuriale, MERCURIALE_FOURNISSEUR, FOURNISSEUR where mercuriale.IDMERCURIALE = MERCURIALE_FOURNISSEUR.IDMERCURIALE and FOURNISSEUR.IDFOURNISSEUR = MERCURIALE_FOURNISSEUR.IDFOURNISSEUR and MERCURIALE.Desactive = 0 and MERCURIALE_FOURNISSEUR.Prix <> 0 and (MERCURIALE.IDMERCURIALE = 3 or MERCURIALE.IDMERCURIALE = 15)
Et j'obtiens le résultat suivant :

ID LIB RS PRIX

3 BABYBEL MINI 22G Fournisseur 1 0.325

3 BABYBEL MINI 22G Fournisseur 2 0.337

15 Camenbert Fournisseur 1 5.313

15 Camenbert Fournisseur 2 5.113

Ce que je souhaiterai obtenir c'est le résultat suivant. Pour chaque produit avoir le fournisseur qui me propose le tarif le plus bas (avec le nom du fournisseur) et celui qui me propose le tarif le plus élevé (avec le nom du fournisseur)
en sachant qu'il peut avoir de 1 à 5 fournisseurs par produit.

Et j'obtiens le résultat suivant :

ID LIB Fournisseur - PRIX - Fournisseur + PRIX +

3 BABYBEL MINI 22G Fournisseur 1 0.325 Fournisseur 2 0.337

15 Camenbert Fournisseur 2 5.113 Fournisseur 1 5.313

Est-ce possible de faire cela ?

J'arrive à obtenir un résultat proche avec cette requête mais sans pouvoir intégrer le fournisseur + et fournisseur -
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 25 26 select ID, LIB, Min(Prix) as M, Max(Prix) as P from ( select mercuriale.IDMERCURIALE as ID, mercuriale.Designation as LIB, FOURNISSEUR.Raison_Sociale as RS, MERCURIALE_FOURNISSEUR.Prix as Prix from mercuriale, MERCURIALE_FOURNISSEUR, FOURNISSEUR where mercuriale.IDMERCURIALE = MERCURIALE_FOURNISSEUR.IDMERCURIALE and FOURNISSEUR.IDFOURNISSEUR = MERCURIALE_FOURNISSEUR.IDFOURNISSEUR and MERCURIALE.Desactive = 0 and MERCURIALE_FOURNISSEUR.Prix <> 0 and (MERCURIALE.IDMERCURIALE = 3 or MERCURIALE.IDMERCURIALE = 15) ) group by ID, LIB
ID LIB PRIX - PRIX +

3 BABYBEL MINI 22G 0.325 0.337

15 Camenbert 5.113 5.313

Bonsoir,

C'est en 1992 que la norme a défini l'usage du mot clé JOIN.
Sans indiscrétion, quel âge avait tu en 1992 ?

Code:

1
2
3
4
5
6
7
8
9
10
from
	mercuriale,
	MERCURIALE_FOURNISSEUR,
	FOURNISSEUR
where
	mercuriale.IDMERCURIALE = MERCURIALE_FOURNISSEUR.IDMERCURIALE
	and FOURNISSEUR.IDFOURNISSEUR = MERCURIALE_FOURNISSEUR.IDFOURNISSEUR
	and MERCURIALE.Desactive = 0
	and MERCURIALE_FOURNISSEUR.Prix <> 0
	and (MERCURIALE.IDMERCURIALE = 3 or MERCURIALE.IDMERCURIALE = 15)

se transforme en (avec aliassage des table au passage) :

Code:

1
2
3
4
5
6
7
from
    mercuriale                  Pdt
    JOIN MERCURIALE_FOURNISSEUR Pdt_Four On Pdt_Four.IDMERCURIALE = Pdt.IDMERCURIALE 
    JOIN FOURNISSEUR            Four     On Four.IDFOURNISSEUR = Pdt_Four.IDFOURNISSEUR 
where Pdt.Desactive = 0
	and Pdt_Four.Prix <> 0
	and Pdt.IDMERCURIALE in(3,15)

Ensuite, les CTE (Common Table expression) sont plus esthétiques que les sous requêtes nommées.

Passons à la demande.

Ta demande peut se résumer ainsi :J'ai pour un produit, éventuellement plusieurs fournisseurs. D'abord, je voudrais le fournisseur le moins cher, Ensuite le plus cher par produit.

En fonction de tout ce qui a été dit, on a cette requête un peu longue à écrire mais simple à dérouler (donc comprendre) et maintenir :

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
25
26
27
28
29
30
31
32
with 
Four_Pdt as
 ( Select Four.Raison_Sociale as RS
         ,Pdt_Four.IDMERCURIALE as Id_Pdt
         ,Pdt_Four.Prix as Prix	
   From MERCURIALE_FOURNISSEUR Pdt_Four On Pdt_Four.IDMERCURIALE = Pdt.IDMERCURIALE 
    JOIN FOURNISSEUR           Four     On Four.IDFOURNISSEUR = Pdt_Four.IDFOURNISSEUR 
 
 )
,Four_Pdt_Moins as 
 (Select rs, Id_Pdt, Min(Prix) as Prix_Moins
  From Four_Pdt
  Group By rs, Id_Pdt
 )
,Four_Pdt_Plus as 
 (Select rs, Id_Pdt, Max(Prix) as Prix_Plus
  From Four_Pdt
  Group By rs, Id_Pdt
  Having count(1) > 2
 )
Select Pdt.IDMERCURIALE as ID 
      ,Pdt.Designation as LIB
	  ,Four_Pdt_Moins.rs as RS_Moins
	  ,Four_Pdt_Moins.Prix_Moins
	  ,Four_Pdt_Plus.rs as RS_Plus
	  ,Four_Pdt_Plus.Prix_Plus
From mercuriale                  Pdt
	inner join Four_Pdt_Moins On Four_Pdt_Moins.Id_pdt = Pdt.IDMERCURIALE
	Left outer join Four_Pdt_Plus On Four_Pdt_Plus.Id_pdt = Pdt.IDMERCURIALE
	where Pdt.Desactive = 0
	and Pdt_Four.Prix <> 0
	and Pdt.IDMERCURIALE in(3,15)

Bonjour,

je te remercie pour ton aide.
Pour répondre premièrement à ta question en 92 j'avais 18 ans et je ne connaissais pas du tout SQL (désolé)
Ensuite effectivement j'utilise JOIN également dans mes requêtes depuis seulement 2 ans puisque c'est là que j'ai commencé à faire du SQL.
enfin tes éléments m'ont été d'une grande aide pour avancer sur ma demande.
Je dois apporter quelques précisions.
Je travaille sous Windev avec HFSQL et malheuresement "With" n'est pas accepté dans les requêtes j'ai donc du partir de ton code avec quelques changements
(J'espere que ce que je vais mettre ne va pas te faire saigner des yeux, si c'est le cas je m'en excuse par avance, je n'ai pas 30 ans d'expérience derrière moi)
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 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Select DISTINCT Pdt.IDMERCURIALE as ID, Pdt.Designation as LIB, Four_Pdt_Moins.rs as RS_Moins, Four_Pdt_Moins.Prix_Moins as P_moins, Four_Pdt_Plus.rs as RS_Plus, Four_Pdt_Plus.Prix_Plus as P_Plus From mercuriale Pdt inner join (Select rs, id_Pdt, Min(Prix) as Prix_Moins From (Select Four.Raison_Sociale as RS ,Pdt_Four.IDMERCURIALE as Id_Pdt ,Pdt_Four.Prix as Prix From mercuriale Pdt join MERCURIALE_FOURNISSEUR Pdt_Four On Pdt_Four.IDMERCURIALE = Pdt.IDMERCURIALE JOIN FOURNISSEUR Four On Four.IDFOURNISSEUR = Pdt_Four.IDFOURNISSEUR ) Four_Pdt Group By rs, Id_Pdt ) Four_Pdt_Moins On Four_Pdt_Moins.Id_pdt = Pdt.IDMERCURIALE Left outer join (Select rs, Id_Pdt, Max(Prix) as Prix_Plus From (Select Four.Raison_Sociale as RS ,Pdt_Four.IDMERCURIALE as Id_Pdt ,Pdt_Four.Prix as Prix From mercuriale Pdt join MERCURIALE_FOURNISSEUR Pdt_Four On Pdt_Four.IDMERCURIALE = Pdt.IDMERCURIALE JOIN FOURNISSEUR Four On Four.IDFOURNISSEUR = Pdt_Four.IDFOURNISSEUR ) Four_Pdt Group By rs, Id_Pdt ) Four_Pdt_Plus On Four_Pdt_Plus.Id_pdt = Pdt.IDMERCURIALE where Pdt.Desactive = 0 and Pdt.IDMERCURIALE in(3,15)
Du coup je me retrouve avec

ID LIB RS_MOIS P_MOINS RS_PLUS P8PLUS

3 BABIBEL FOUR 1 0.325 FOUR 2 0.337

3 BABIBEL FOUR 1 0.325 FOUR 1 0.325

3 BABIBEL FOUR 2 0.337 FOUR 2 0.337

15 CAMEMBERT FOUR 1 5.313 FOUR 2 5.396

15 CAMEMBERT FOUR 1 5.313 FOUR 1 5.313

15 CAMEMBERT FOUR 2 5.396 FOUR 2 5.396

J'ai ajouté dans la clause where
and (p_moins <= p_plus and rs_moins <> rs_plus)

J'ai 2 soucis qui apparaissent sur l'ensemble des enregristrements :
1/ il est possible qu'un seul fournisseur propose le produit et du coup le produit n'apparait pas dans la liste
2/ Si les 2 fournisseurs proposent le produit au même prix, je me retrouve avec 2 lignes dans le résultat

Puis-je savoir si ma requête est correcte déjà et me donner des pistes pour que je puisse trouver la solution final

Merci

14/06/2019, 20h52
Michel.Priori

Bonsoir,

Citation:

Envoyé par GabMaster

Je travaille sous Windev avec HFSQL

8O Pourquoi poster dans le forum "MS SQL Server" ? 8O

Je ne connais pas HFSQL.
Faut voir si le plan d’exécution ne respecte effectivement pas l’écriture SQL mais fait bien un plan logique en partant des tables qui ont le moins de lignes utiles.
Sinon ça risque de piquer un peu question performances.

Citation:

Envoyé par GabMaster

J'ai 2 soucis qui apparaissent sur l'ensemble des enregristrements :
1/ il est possible qu'un seul fournisseur propose le produit et du coup le produit n'apparait pas dans la liste
2/ Si les 2 fournisseurs proposent le produit au même prix, je me retrouve avec 2 lignes dans le résultat

Oups :fessee:
Il était tard quand j'ai posté :oops:

Il faut faire un peu plus de travail que ce que j'ai posté.
L'erreur vient du fait que j'ai fais un raccourcis grossier en ajoutant RS dans les CTE : du coup on a le prix min (ou max) par produit ET par fournisseur => inutile vu qu'un fournisseur ne devrait fournir qu'une seule fois une même référence !
Bien évidemment le prix min ou max est à rechercher par id_Pdt uniquement.

bon, vu que t'as compris les sous requête l'article : https://www.developpez.net/forums/bl...cente-critere/ est très instructif ;)
En absence de CTE on peut aussi faire des vues...

Nota : pour le problème des fournisseurs au même prix, il faudra ajouter un critère pour n'en garder qu'un.
On pourrait imaginer une colonne "Indicateur de préférence" qui soit un numérique unique ; le plus fort est sélectionné.

ID	LIB	RS	PRIX
3	BABYBEL MINI 22G	Fournisseur 1	0.325
3	BABYBEL MINI 22G	Fournisseur 2	0.337
15	Camenbert	Fournisseur 1	5.313
15	Camenbert	Fournisseur 2	5.113

ID	LIB	Fournisseur -	PRIX -	Fournisseur +	PRIX +
3	BABYBEL MINI 22G	Fournisseur 1	0.325	Fournisseur 2	0.337
15	Camenbert	Fournisseur 2	5.113	Fournisseur 1	5.313

ID	LIB	RS_MOIS	P_MOINS	RS_PLUS	P8PLUS
3	BABIBEL	FOUR 1	0.325	FOUR 2	0.337
3	BABIBEL	FOUR 1	0.325	FOUR 1	0.325
3	BABIBEL	FOUR 2	0.337	FOUR 2	0.337
15	CAMEMBERT	FOUR 1	5.313	FOUR 2	5.396
15	CAMEMBERT	FOUR 1	5.313	FOUR 1	5.313
15	CAMEMBERT	FOUR 2	5.396	FOUR 2	5.396