Problème de jointure avec condition et minimum

16 juil. 2006, 13:58

Bonjour, voici mon problème...j'essaie de résumer mon soucis:

J'ai une première table reprenant des contrats proposés identifiés par un id_contrat:

id_contrat|nom|auteur|etat|....blabla

J'ai une seconde table avec les offres pour ces contrats, j'ai donc plusieurs propositions pour un même contrat!

id_contrat|auteur_prop|montant|date_expiration|etat|...

En gros, je voudrais lister les contrats avec pour chacun d'eux la meilleur offre valable...

Pour l'instant je fais:

SELECT * from x_bd_contrat WHERE x_bd_contrat.etat='activé'

...

puis je boucle sur tous les id_contrat et je fais

SELECT MIN(montant) as min FROM x_offre WHERE id_contrat='".$data['id_contrat']."' AND date_expiration>NOW() AND etat='activé'

Ca marche parfaitement...mais je ne parviens pas à classer les contrats en fonction des meilleurs...

Donc,j 'aiemarais réaliser la requête en une seule fois, j'essaie

SELECT *, MIN(x_offre.montant) as min
from x_bd_contrat
LEFT JOIN x_offre ON x_offre.id_contrat=x_bd_contrat.id_contrat
WHERE x_bd_contrat.etat='activé' and x_offre.date_expiration>NOW()
and x_offre.etat='activé'
GROUP by x_bd_contrat.id_contrat

Mais la clause sur la date d'expiration empêche la requête de s'effetuer corrcetement...

Avez-vous une idée comment traiter ce problème? Soit par ce biais ou par tout autre moyen?
Merci

16 juil. 2006, 15:21

Je ne sais pas si ça tient à la version utilisée de MySQL, mais j'ai fait le test suivant: j'ai commencé par créer deux tables et ajouté un jeu de test :

Code : Tout sélectionner

CREATE TABLE `offres` (
  `off_id` int(10) unsigned NOT NULL auto_increment,
  `cont_id` int(10) unsigned NOT NULL default '0',
  `off_auteur` varchar(45) NOT NULL default '',
  `off_montant` float(10,2) NOT NULL default '0.00',
  `off_date_expiration` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`off_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

+---------+----------+-------------+-----------+
| cont_id | cont_nom | cont_auteur | cont_etat |
+---------+----------+-------------+-----------+
|       1 | test1    | dupont      | inactif   |
|       2 | test2    | dupond      | actif     |
|       3 | test3    | duponk      | actif     |
+---------+----------+-------------+-----------+

Create Table: CREATE TABLE `contrat` (
  `cont_id` int(10) unsigned NOT NULL auto_increment,
  `cont_nom` varchar(45) NOT NULL default '',
  `cont_auteur` varchar(45) NOT NULL default '',
  `cont_etat` enum('actif','inactif') NOT NULL default 'actif',
  PRIMARY KEY  (`cont_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

+--------+---------+------------+-------------+---------------------+
| off_id | cont_id | off_auteur | off_montant | off_date_expiration |
+--------+---------+------------+-------------+---------------------+
|      1 |       1 | Durand     |      250.00 | 2006-07-24          |
|      2 |       1 | Durant     |      255.00 | 2006-07-24          |
|      3 |       1 | Durann     |      245.00 | 2006-07-24          |
|      4 |       1 | Durank     |      155.00 | 2006-06-24          |
|      5 |       2 | Durand     |      250.00 | 2006-07-24          |
|      6 |       2 | Durant     |      255.00 | 2006-07-24          |
|      7 |       2 | Durann     |      220.00 | 2006-07-24          |
|      8 |       2 | Durank     |      155.00 | 2006-06-24          |
|      9 |       3 | Durand     |      250.00 | 2006-07-24          |
|     10 |       3 | Durant     |      255.00 | 2006-07-24          |
|     11 |       3 | Durann     |      195.00 | 2006-07-24          |
|     12 |       3 | Durank     |      155.00 | 2006-06-24          |
+--------+---------+------------+-------------+---------------------+

Partant de là, il est relativement facile d'identifier visuellement les lignes qui devraient ressortir : les offres 4, 8 et 12 sont expirées, donc même si ce sont les meilleures offres, elles ne peuvent être sélectionnées, on en arrive donc aux offres 3, 7 et 11. Mais le contrat 1 est inactif, donc on réduit encore aux offres 7 et 11 seulement. Voici la requête que j'ai utilisé et qui ne diffère pas tellement de la tienne :

Code : Tout sélectionner

mysql> SELECT *, MIN(o.off_montant) as 'Meilleure offre'
    -> FROM contrat AS c, offres AS o
    -> WHERE o.cont_id = c.cont_id
    ->   AND c.cont_etat='actif'
    ->   AND o.off_date_expiration > NOW()
    -> GROUP by c.cont_id;
+---------+----------+-------------+-----------+--------+---------+------------+-------------+---------------------+-----------------+
| cont_id | cont_nom | cont_auteur | cont_etat | off_id | cont_id | off_auteur | off_montant | off_date_expiration | Meilleure offre |
+---------+----------+-------------+-----------+--------+---------+------------+-------------+---------------------+-----------------+
|       2 | test2    | dupond      | actif     |      5 |       2 | Durand     |      250.00 | 2006-07-24          |          220.00 |
|       3 | test3    | duponk      | actif     |      9 |       3 | Durand     |      250.00 | 2006-07-24          |          195.00 |
+---------+----------+-------------+-----------+--------+---------+------------+-------------+---------------------+-----------------+
2 rows in set (0.01 sec)

Il te reste à t'inspirer de ça pour ajuster selon tes propres noms de tables et de champs.

16 juil. 2006, 15:39

Merci, je vais voir ce que je peux faire...je vous tiens au courant!

PS: Sinon autre petite question, comment afficher des tables comme tu l'as fait?

16 juil. 2006, 15:43

Je fais mes requêtes avec le client MySQL en ligne de commande, ensuite, je fais un copier/coller tout simplement.

16 juil. 2006, 17:34

Okai, c parfait! Ca fonctionne...

Mais il me reste deux petites questions:

1°) Je voudrais qu'il liste tous les contrats, même ceux pourlesquels il n'y aurait pas t'offre valable...Est-ce possible?
Donc, dans ton exemple, il faudrait qu'il me retourne aussi le cont_id 1...

2°) Plus général, au départ, j'utilisais un LEFT JOIN, hors d'après ce que tu m'as montré, ce n'est pas obligatoire...Quand va-t-on utiliser le LEFT JOIN?

En tout cas merci beaucoup...je suis débarassé d'un gros problème, le reste c'est des détails!

Problème de jointure avec condition et minimum

Qui est en ligne