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
