Didn't receive answer to message below. Could someone please take a look?
TIA.
- Sheryl
----- 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
