Man, you're awesome. Only two typos in the whole procedure. However, for the
life of me, I'm puzzled over how it works. If you have more patience can you
explain?
Correct query (table is singular, not PHPAUCTIONS_...):
CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid
FROM PHPAUCTION_bids GROUP BY auction;
The temporary table tmpWinners doesn't even contain a field for the ID of the
winning bidder. This is the first puzzling point and significant diference
between our two solutions.
Correct query (changed line 8 from "AND b.bit...):
SELECT
CONCAT(a.title, " (", LEFT(b.auction,4), ")") AS FullTitle
, SUBSTRING_INDEX(u.name, " ", 1) AS fname
, b.bid
FROM tmpWinners AS w
INNER JOIN PHPAUCTION_bids AS b
ON b.auction = w.auction
AND b.bid = w.winningbid
INNER JOIN PHPAUCTION_users AS u
ON b.bidder=u.id
INNER JOIN PHPAUCTION_auctions AS a
ON b.auction=a.id
ORDER BY FullTitle
LIMIT 5;
-> \g
+--------------------------------------------------------------+---------------+---------+
| FullTitle | fname
| bid |
+--------------------------------------------------------------+---------------+---------+
| 101 Dalmations, Oliver & Company, A Goofy Movie - VHS (d7b4) | Saori
| 3.0000 |
| 2 dozen chocolate chip cookies (30c1) | ucantoutbidme
| 11.0000 |
| 2 dozen chocolate chip cookies (a3aa) | Donna
| 7.0000 |
| 2 dozen chocolate chip cookies (d8e5) | Donna
| 8.0000 |
| 2 Gold Rings (d9c1) | t
| 26.0000 |
+--------------------------------------------------------------+---------------+---------+
5 rows in set (0.15 sec)
mysql>
So, the SELECT query goes through the tmpWinniing table and, for each record,
finds a record in the bids table that has the same item ID and same bid price.
It then uses the bidder's ID it found to look up the bidder's name, and the
auction item's ID to look up the item's title.
It seems to me that the significant difference between our solutions is your
use of two conditional clauses in the INNER JOIN between tmpWinner and
PHPAUCTION_bids. I don't think I've ever seen a join done on more than one
field between two tables before. Would this query still work if more than one
person bid the same amount on the same item? The business rules built into
phpAuction prevent this, but in a more generic situation, would this query
still work correctly? I guess one anwer to this is 'yes,' because the MAX()
function returns the first of two equal maximum values it finds, doesn't it?
Despite my puzzlement at how you were able to come up with such a great
solution, I'm very grateful for your help and explanations. If you're ever in
Baltimore, MD, I owe you a beer. Thanks.
-Kevin
>>> <[EMAIL PROTECTED]> 11/04/05 11:22AM >>>
This is such a FAQ that they put the answer in the manual:
http://dev.mysql.com/doc/refman/5.0/en/index.html
What you are looking for is the row that contains the maximum bid for each
itemname. The easiest first step it to actually determine what the highest
bid for each item actually is then use that information to build the rest
of what you wanted.
The most portable and easiest to write solution to this is the two-table
method ( I think I divined your column names correctly, maybe not...):
# begin example #
CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid
FROM PHPAUCTIONS_bids GROUP BY auction;
SELECT
CONCAT(a.title, " (", LEFT(b.auction,4), ")") AS FullTitle
, SUBSTRING_INDEX(u.name, " ", 1) AS fname
, b.bid
FROM tmpWinners AS w
INNER JOIN PHPAUCTION_bids AS b
ON b.auction = w.auction
AND b.bit = w.winningbid
INNER JOIN PHPAUCTION_users AS u
ON b.bidder=u.id
INNER JOIN PHPAUCTION_auctions AS a
ON b.auction=a.id
ORDER BY FullTitle
LIMIT 35;
DROP TEMPORARY TABLE tmpWinners;
# end example #
Does that make sense? You should be able to expand on that pattern to
build whatever list you want. I showed the full-chain of how each table
relates to another but you could have simplified the query above, can you
see where?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
