I got it to work, if anyone's interested (see message below). Here is the
SQL:
DROP TABLE IF EXISTS ProgramRoyalties;
CREATE TEMPORARY TABLE ProgramRoyalties
SELECT UtilityID, max(Royalty) as MaxRoyalty
FROM royalties
GROUP BY UtilityID;
SELECT u.UtilityID, u.UtilityIcon, u.UtilityName, u.Version,
u.ShortDescription, u.MinorReleaseDate,
a.LastName, r.UtilityID, r.AuthorID, r.Royalty, pr.UtilityID,
pr.MaxRoyalty
FROM utilities u, authors a, royalties r, ProgramRoyalties pr
WHERE
u.UtilityID = r.UtilityID AND
r.UtilityID = pr.UtilityID AND
a.AuthorID = r.AuthorID AND
r.Royalty = pr.MaxRoyalty;
What I didn't understand was that the temporary table is simply a look-up
table for the max royalty for each utility. I use this in the WHERE clause
of the SELECT statement.
Sheryl Canter
Permutations Software
www.permutations.com
----- Original Message -----
From: "Sheryl Canter" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, June 22, 2003 2:46 PM
Subject: need help with subselect workaround
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
