This is a reprise of a question I asked some months ago. Bruce Feist and Tore Bostrup
gave me some untested code to try. I've only now been able to try it, and it's not
quite working right. Here's a recap of the problem I'm trying to solve:
My Web host is running MySQL 3.23, which doesn't support subselects. I have a Web site
that displays a list of programs for sale (shareware). The list displays the authors'
names. In some cases, more than one author works on a program. In this case, I want to
display the name of the lead author. I define this programmatically as the author who
earns the highest royalty rate. I have tables like this:
royalties table (primary key is a combination of AuthorID and UtilityID):
AuthorID UtilityID Royalty
--------------------------------------
Author1 Utility1 0.15
Author2 Utility1 0.10
Author3 Utility2 0.25
Author4 Utility3 0.05
Author5 Utility3 0.20
authors table:
AuthorID FirstName LastName
----------------------------------------
Author1 Joe Smith
Author2 Brian Jones
Author3 Jeff Tucker
Author4 Michael Moore
Author5 Mark Mann
utilities table:
UtilityID ProgramName
----------------------------------------
Utility1 ProgName1
Utility2 ProgName2
Utility3 ProgName3
This is my SQL code:
DROP TABLE IF EXISTS ProgramRoyalties;
CREATE TEMPORARY TABLE ProgramRoyalties
SELECT AuthorID, UtilityID, Royalty, max(Royalty) as MaxRoyalty
FROM royalties
WHERE Royalty = MaxRoyalty
GROUP BY UtilityID;
Unfortunately, the above produces a table with nothing in it. If I take out the WHERE
clause, I get one line per group (per utility), but the author isn't necessarily the
one with the highest royalty rate. It seems like the first author in the list is being
selected.
The SELECT statement for using the above table (once it's properly populated) is:
SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version, u.ShortDescription,
u.MinorReleaseDate,
a.LastName, pr.UtilityID, pr.AuthorID
FROM utilities u, authors a, ProgramRoyalties pr
$WhereClause
u.UtilityID = pr.UtilityID AND a.AuthorID = pr.AuthorID;
How do I get the correct data into the temporary table?? Using the above sample data,
I'd want it to look like this:
ProgramRoyalties table (primary key is a combination of AuthorID and UtilityID):
AuthorID UtilityID Royalty
--------------------------------------
Author1 Utility1 0.15
Author3 Utility2 0.25
Author5 Utility3 0.20
Thanks in advance for your help.
Sheryl Canter
Permutations Software
www.permutations.com
