Sub Query

[Extier]

Hi everyone,

I am new to coding and have been trying to get a query to work in php/mysql. The query is as follows:

SELECT auc_bids.bidder, auc_bids.bid, auc_auctions.id, auc_auctions.auctionid, auc_auctions.user, auc_auctions.title, auc_auctions.starts, auc_auctions.description, auc_auctions.pict_url, auc_auctions.pict_url2, auc_auctions.pict_full1, auc_auctions.pict_full2, auc_auctions.minimum_bid, auc_auctions.reserve_price, auc_auctions.buynow, auc_auctions.auction_type, auc_auctions.increment, auc_auctions.ends, auc_auctions.closed

FROM auc_auctions INNER JOIN auc_bids ON auc_auctions.id = auc_bids.auction

WHERE (((auc_bids.bid)=(select max(bid) from auc_bids where auc_bids.auction=auc_auctions.id)));

Having searched around, I understand now that mysql doesn't like sub-queries... at least for version pre-4.1. My question is how would I get this query to work without a sub-query?

Thanks in advance for any help/insight.

[freebie]

Here's a query I found that uses COALESCE() in combination with a left outer join. COALESCE() is a mysql comparison operator (that returns the first non-null value). I really can't say that I understand how COALESCE is used in this query though, but I think it's the root of your answer.

SELECT I.itemNum
, coalesce(max(B.bidAmount),0) AS bidAmount
from items AS I
left outer join bids AS B
on I.itemNum = B.itemNum
GROUP BY itemNum

see the following URL for documentation on the COALESCE() comparison operator
http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html