Can you do this using join?

[richrock]

I'm trying to achieve the impossible I think, but most programmers don't believe in impossible, just try to make things work... so:

 

I am trying to get a price from a database. This is in the middle of a much larger query returning search results....

 

The problem is :

 

The item table has the information and a Buy It Now price.

The bidding table has the bids that have been placed on an item.

 

I have done a left join on id > bid_id, so it would match, but how can I get a maximum bid price if there is one? Like I said, it's part of a larger query, and I don't think I can set another query into it without some performance hit.... Here's the code snippets :

 

$where .= " left join jos_bids bd on a.id = bd.id_offer ";
$where .= " where close_offer=0 and start_date<=now() and published=1 ";

then I put a rough piece of code to sort the Buy It Now options :

if (($_POST['src_price_range'] == 'void') || ($_POST['src_price_range'] == NULL) || ($_POST['src_price_range'] == '')) {

   } else { if ($_POST['src_price_range']) {
       // If Bids not Placed
       if ($_POST['src_price_range'] == '1') { $where .= " and a.BIN_price > 0 and a.BIN_price < 10 "; }
       if ($_POST['src_price_range'] == '2') { $where .= " and a.BIN_price > 10 and a.BIN_price < 25 "; }
       if ($_POST['src_price_range'] == '3') { $where .= " and a.BIN_price > 25 and a.BIN_price < 50 "; }
       if ($_POST['src_price_range'] == '4') { $where .= " and a.BIN_price > 50 and a.BIN_price < 100 "; }
       if ($_POST['src_price_range'] == '5') { $where .= " and a.BIN_price > 100 and a.BIN_price < 200 "; }
if ($_POST['src_price_range'] == '6') { $where .= " and a.BIN_price > 200 "; }
// If Buy It Now option selected
$where .= " bd.bid_offer == NULL ";
       // If bid placed
       }
   }

 

Which I'll tidy up afterwards. There are 6 price ranges to filter, and it's just bugging the heck out of me... I'm trying to read the MySQL docs, but they seem so abstract I don't understand them that easily (the PHP ones are much better IMO)

 

Essentially I'm trying to see:

If there is a BIN price, and no bids, use the BIN price.

If there is a bid offer, use the highest bid offer.

If there is neither, use the start_price.

 

Any ideas how I can get this sorting?

 

EDIT : here's a sample of a full query that ISN'T working :

 

select count(*) from jos_bid_auctions a left join jos_bids bd on a.id = bd.id_offer where close_offer=0 and start_date<=now() and published=1 and (a.cat=1 or cats.parent=1) bd.bid_offer > 0 and bd.bid_offer < 1000

[kickstart]

Hi

 

I think I would use outer joins to get the (possible) max bid info, and then use a case statement within the select to decide which of the various prices to bring back.

 

All the best

 

Keith

[richrock]

Thanks!

 

Yeah, I've figured to do the join and still playing around with trying to get the max bid info. I noticed a couple of typos in my query, so they've been tidied up - I can get all results on the bid info, but never used case..

 

I'll keep digging, and if anyone has ideas - thanks!

 

And this is the current state of the query - returns two bids on the same item, not the max one:

 

select DISTINCT(a.id) as aucid, a.title, MAX(b.bid_price) as bid_price from jos_bid_auctions a left join jos_bids
b on a.id = b.id_offer where b.bid_price IS NOT NULL group by b.bid_price order by b.bid_price desc;

[kickstart]

Hi

 

Just knocked up a couple of crude test tables (eg, INT prices) and had a quick play.

 

This basically seems to give you what you want:-

 

SELECT * , (
CASE WHEN b.Bid IS NULL 
AND BinPrice IS NOT NULL 
THEN BinPrice
WHEN b.Bid IS NOT NULL 
THEN b.Bid
ELSE StartPrice
END 
) AS FinalPrice
FROM jos_bid_auctions a
LEFT JOIN jos_bids b ON a.Id = b.AuctionId
AND b.Bid = ( 
SELECT MAX( Bid ) 
FROM jos_bids c
WHERE c.AuctionId = b.AuctionId )

 

This does rely on a subselect (and a bit dubious on doing it on a max bid, when in theory there could be duplicate max bids for an item). Depending on how the data is checked when stored you could probably use the Max(ID) field instead.

 

Alternative , but in this case not bringing back any id of the winning bid (just the value).

 

SELECT * , (
CASE WHEN b.Bid IS NULL 
AND BinPrice IS NOT NULL 
THEN BinPrice
WHEN b.Bid IS NOT NULL 
THEN b.Bid
ELSE StartPrice
END 
) AS FinalPrice
FROM jos_bid_auctions a
LEFT JOIN (
SELECT MAX( Bid ) Bid, AuctionId
FROM jos_bids
GROUP BY AuctionId
) AS b ON a.Id = b.AuctionId

 

Hope this gives you an idea.

 

All the best

 

Keith

[richrock]

Excellent - not much chance of a duplicate max bid, the new bid HAS to be higher than the current.  I'll give this a shot!

 

Thanks v much.