[MySQL]Join et max ne donne que une seule ligne de résultat

Marsh Posté le 10-07-2009 à 13:49:59

Salut,

voici ma requête :

Code :

SELECT a.id, a.auction_type, a.initial_price, a.bid_type, a.picture, a.end_time, a.year, a.make, a.type,
a.start_date AS cert_startdate, a.end_date AS cert_enddate, a.close_offer,
b.name as currency,
m.name as make,
MAX(n.bid_price) as max_bid
FROM jos_bid_auctions a
LEFT JOIN jos_bid_currency b ON a.currency = b.id
LEFT JOIN jos_bid_makes m ON m.id = a.make
LEFT OUTER JOIN jos_bids n on n.id_offer = a.id
WHERE a.published =1
AND a.bid_type = '0'
AND a.close_offer !=1
AND a.end_date >= NOW()
AND a.start_date <= NOW()
ORDER BY a.end_date ASC
LIMIT 5

Je voudrais retourner tous les max des bid_price pour chaque a.id
Et en fait je pense que la, on prend le max des bid_price pour tout le résultat ce qui ne renvoit qu'un seul résultat.
Y a-t-il un moyen de s'en sortir ?

Marsh Posté le 10-07-2009 à 13:49:59

Marsh Posté le 10-07-2009 à 14:07:46

Code :

SELECT a.id, a.auction_type, a.initial_price, a.bid_type, a.picture, a.end_time, a.year, a.make, a.type,
a.start_date AS cert_startdate, a.end_date AS cert_enddate, a.close_offer,
b.name as currency,
m.name as make,
MAX(n.bid_price) as max_bid
FROM jos_bid_auctions a
LEFT JOIN jos_bid_currency b ON a.currency = b.id
LEFT JOIN jos_bid_makes m ON m.id = a.make
LEFT OUTER JOIN jos_bids n on n.id_offer = a.id
WHERE a.published =1
AND a.bid_type = '0'
AND a.close_offer !=1
AND a.end_date >= NOW()
AND a.start_date <= NOW()
GROUP BY a.id
ORDER BY a.end_date ASC
LIMIT 5

C'est mieux la ?

Message édité par Profil supprimé le 10-07-2009 à 14:09:41

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed

Name *

Email *

URL

Message *