How it works... "KEVIN ZEMBOWER" <[EMAIL PROTECTED]> wrote on 11/04/2005 12:40:50 PM:
> 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. > The CREATE TABLE command can make tables out of query results. No need to define the columns as they will be auto-typed to fit the results. In this case I chose to create a TEMPORARY table instead of a static table. http://dev.mysql.com/doc/refman/4.1/en/create-table.html > 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. exactly. > > 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? You have surmised correctly. If more than one row matches your "maximum" condition (as determined by the rows in tmpWinners) then all matching rows will be returned in the final query. > 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 SQL is a language that tends towards patterns. Learn the patterns and you get better at SQL. This happens to be a very well-known pattern so I can't take any of the credit as it was around long before I started programming. I am just glad I could share. Do I still get that beer? ;-) About multiple terms in the ON phrases of the JOIN clauses... It is sometimes to your advantage to move terms out of the WHERE clause and into the ON clause (especially when it comes to the LEFT and RIGHT joins). Not only can it make the logic of your query correct but sometimes it opens the optimizer up to using indexes it may not have considered before. Almost any logical statement is permissible in an ON clause. See http://dev.mysql.com/doc/refman/5.0/en/join.html for more details on what will and won't work based on the order your tables are JOINed into the query. Sorry about the typos. Glad they didn't mess you up :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
