Help with subSelect or JOIN query

[34534534534555]

Hi

I am running MySQL client version: 3.23.49

I have a table: Auction and another table: Bid (for accepting bids on an auction).

In the Bid table is: Auctionid, Bid_username, Bidprice

I am trying to SELECT the max bid from the bid table for a particular auction. What i have is

$sql = "SELECT auction.auctionid, auction.bookcondition, book.edition, bid.bidprice
		FROM auction, book
		LEFT JOIN bid ON auction.auctionid=bid.auctionid
		WHERE sellerusername <> '$username'
		AND auction.isbn = book.isbn&quot;;

...which returns a new line for every bid in the auction table. Could someone please show me how to just get the MAX bidprice?

I am vaguely aware of SubSelect (i am not sure that this mySQL supports it)

Many TIA


| Feedback is always appreciated as this will help to further our knowledge as well |

 

[swampBoogie]

SELECT auction.auctionid, auction.bookcondition, 
       book.edition, max(bid.bidprice)
  FROM book join auction
    ON book.isbn = auction.isbn
  LEFT JOIN bid 
    ON auction.auctionid=bid.auctionid
 WHERE sellerusername <> '$username'
 GROUP BY auction.auctionid, 
     auction.bookcondition, book.edition

 

[34534534534555]

Hi, thanks for your help, however, i got this message:

Cannot query the database: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ÊÊ LEFT JOIN bid ÊÊÊÊ ON auction.auctionid=bid.auctionid Ê


| Feedback is always appreciated as this will help to further our knowledge as well |

 

[34534534534555]

Does anyone else have an idea? I have tried, but i cannot get my head around joins.


| Feedback is always appreciated as this will help to further our knowledge as well |

 

[swampBoogie]

'ÊÊ

Where does those come from?

 

[cocobingo]

Hello!

Maybe you could try :

SELECT auction.auctionid, auction.bookcondition, book.edition, bid.bidprice
FROM auction INNER JOIN book ON (auction.isbn = book.isbn)
LEFT JOIN bid ON (auction.auctionid=bid.auctionid)
WHERE sellerusername <> '$username'
ORDER BY bid.bidprice DESC
LIMIT 1

That would return only the first 1 row, and since the query is sorted by bid.bidprice in descneding order, that should be the highest bid.

Arnaud

 

[34534534534555]

swampBoogie, i am not sure where the EE's came from, other than that they were generated by my error script when i tried to run that SQL.

cocobingo, your code worked just as required with a little addition.

"SELECT auction.auctionid, auction.bookcondition, book.edition, bid.bidprice
		FROM auction INNER JOIN book ON (auction.isbn = book.isbn)
		LEFT JOIN bid ON (auction.auctionid=bid.auctionid)
		WHERE sellerusername <> '$username' AND auction.isbn = $select
		ORDER BY bid.bidprice DESC
		LIMIT 1&quot;;

thank you both very much


| Feedback is always appreciated as this will help to further our knowledge as well |