[RESOLVED] Do I need to use a nested query

ade234uk

I have created two simple tables, one for auctions and one for bids and manaully populated this through PhpMyAdmin. For trial purposes Auction 21 has 3 bids on it.

I have managed to join the two tables together using the INNER JOIN statement and can output the results of the auctions.

$result = mysql_query("SELECT * FROM auctions a INNER JOIN bids b ON a.auction_id = b.auction_id GROUP BY a.auction_id");

I am trying to order the bid_amount from the bids table to show the highest bid when it is printed to screen, but what it is doing is just showing me the first. Auction 21 has 3 bids and 57.00 is the highest bid.

Here are my tables

// auctions
auction_id
auction_title
start_price

// bids
auction_id
member_id
bid_amount
bid_date

Roger_Ramjet

use MAX(bid_amount)

ade234uk

Brilliant thank you very. I could not get it working by using an inner join but used the following sql statment, and it shows the highest bid.

$result = mysql_query("SELECT bids,start_price, auctions.auction_id, auction_title, MAX(bid_amount)
FROM auction_bids, auctions WHERE auction_bids.auction_id = auctions.auction_id GROUP BY auction_bids.auction_id");

If I could see how this would work with an INNER JOIN there would be no need for the above code.

Thank you for your help.

tomhath

FROM auction_bids, auctions
WHERE auction_bids.auction_id = auctions.auction_id

FROM auction_bids
INNER JOIN auctions
ON auction_bids.auction_id = auctions.auction_id

Same thing. The second is a clearer way of writing the first. MySQL might insist on having a WHERE clause in the second; try "WHERE 1=1" to make it happy.

Your problem before was using SELECT * with a GROUP BY. Every row in the result set was unique, so nothing was grouped.

Roger_Ramjet

Nah, Tom, mysql does not work like that. When you 'take advantage' of the ability to select columns not mentioned in the group by, it does not do full grouping on all columns. What was happening to Ade was that it grouped on the auction_id it then just selected the other columns from the first row in each group. If it did do full grouping on every column then it would return all rows because at least one column would be different.

Odd behaviour true, but how mysql works - I'm sure it's mentioned in the manual but I'm not gonna spend time looking for the ref.

Other rdbms don't allow you to select columns not grouped on because of this uncertainty in the results. Some call it random, but it is not. The results will always be from the first row in the group - which may or may not correspond to the desired results in a random way according to the order of the rows found during grouping.

tomhath

Roger -

Okay, I stand corrected.

-Tom

ade234uk

Thank you all for your help. I will mark this thread resolved. My Intention was too get the highest bid displayed and this has now been achieved which is great.

I can now move on the next part of creating an auction
I can do this part

Thank you all.