For readability, using the INNER JOIN clause makes it immediately clear what/how you are joining the tables, but it isn't too hard to read that from a WHERE clause either. Of course, for other types of join, definitely use the JOIN statement.
It is *possible* that the optimizer may treat an INNER JOIN differently from a join in the WHERE clause - I don't know how the MySQL optimizer works. Regards, Tore. ----- Original Message ----- From: "Sheryl Canter" <[EMAIL PROTECTED]> To: "Tore Bostrup" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, February 28, 2003 7:58 AM Subject: Re: SELECT DISTINCT question > Tore, > > Thanks very much for this. Question: Is it better to use INNER JOIN than a > WHERE clause (for readability)? They do the same thing, don't they? > > I will try this out as soon as a finish another piece of my site. I'm under > a big deadline crunch. > > I'll be interested to see if others know of other ways of handling this. > > - Sheryl > > > ----- Original Message ----- > From: "Tore Bostrup" <[EMAIL PROTECTED]> > To: "Sheryl Canter" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, February 28, 2003 1:41 AM > Subject: Re: SELECT DISTINCT question > > > DISTINCT works on the result set and across the entire select list. It will > suppress duplicate result *rows*. > > This is an interesting problem when using MySQL due to the lack of support > for nested SELECTs. In other database systems, I'd use a correlated > subquery in the where clause, but with MySQL a different solution would be > required. > > There may be a more direct way, but the following works on both version 4 > and 3.23: > > DROP TABLE IF EXISTS tmproymax; > > CREATE TEMPORARY TABLE tmproymax > (ProgramID int, Royalty decimal(10,2)); > > INSERT INTO tmproymax > SELECT ProgramID, > Max(Royalty) as MaxRoyalty > FROM royalties > GROUP BY ProgramID; > > SELECT R.ProgramID, > R.Royalty, > A.AuthorID, > A.FirstName, > A.LastName > FROM authors as A > INNER JOIN royalties as R > ON A.AuthorID = R.AuthorID > INNER JOIN tmproymax as RM > ON R.ProgramID = RM.ProgramID > AND R.Royalty = RM.Royalty; > > For using this with PHP, I'm pretty sure you'll have to run each statement > separately, but using the same connection, and you should get the correct > result from the last select. > > I'd be interested to hear if there is another trick to working without a > correlated subquery for finding details off of a row identified by Min(), > Max(), etc. > > I tried using a CREATE TEMPORARY TABLE tmproymax SELECT ... but couldn't get > the aggergate column named (in version 4), so I could use it in the join in > the last statement. For version 3.23, that construct would work: > > DROP TABLE IF EXISTS tmproymax; > > CREATE TEMPORARY TABLE tmproymax > SELECT ProgramID, > Max(Royalty) as Royalty > FROM royalties > GROUP BY ProgramID; > > SELECT R.ProgramID, > R.Royalty, > A.AuthorID, > A.FirstName, > A.LastName > FROM authors as A > INNER JOIN royalties as R > ON A.AuthorID = R.AuthorID > INNER JOIN tmproymax as RM > ON R.ProgramID = RM.ProgramID > AND R.Royalty = RM.Royalty; > > > HTH, > Tore. > > > > ----- Original Message ----- > From: "Sheryl Canter" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, February 26, 2003 11:49 AM > Subject: SELECT DISTINCT question > > > > I need help with a query. I have a 'royalties' table that looks like this: > > > > AuthorID ProgramID Royalty > > -------------------------------------- > > Author1 Program1 0.15 > > Author2 Program1 0.10 > > Author3 Program2 0.25 > > Author4 Program3 0.05 > > Author5 Program3 0.20 > > > > The primary key of this table is a combination of AuthorID and Program ID. > > Author information is stored in a separate table: > > > > AuthorID FirstName LastName > > ---------------------------------------- > > Author1 Joe Smith > > Author2 Brian Jones > > Author3 Jeff Tucker > > Author4 Michael Moore > > Author5 Mark Mann > > > > The main page of my Web site has a program list that includes the program > > name and author name (and other information). I want it to show the author > > receiving the highest royalty amount. Right now I'm not considering > > the possibility that more than one author can work on a program (since > > currently none is), and my SELECT statement looks similar to this: > > > > SELECT * > > FROM programs p, authors a, royalties r > > WHERE p.ProgramID = r.ProgramID AND a.AuthorID = r.AuthorID > > > > I could change this to SELECT DISTINCT * ..., but then which author would > I > > get? If it's always the first encountered row, then could I avoid checking > > the royalty by always inserting the authors into the table in the correct > > order? (I know this is sloppy.) What is the rule used by SELECT DISTINCT > > to choose which row to return? > > > > If I wanted to do it right and select the author receiving the maximum > > royalty, how would I adjust the SELECT statement? > > > > TIA, > > > > - Sheryl > > > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
