hi.
i would suggest doing this in 2 queries, one to get the penalties, and one
to get the goals...as you can only really get the "top 10" for one of them.
if you want the top 10 leaders in penalty minutes, and the goals scored for
that player, you can join the goals table in there as well and SUM() those
up...
select
teams.TEAMNAME,
players.PLAYERID,
players.FIRSTNAME,
players.LASTNAME,
SUM(penalty.PENALTYMINUTES) as TPM
from
players left join penalty on players.PLAYERID = penalty.PLAYERID,
teams
where
players.TEAMID = teams.TEAMID
group by players.PLAYERID
order by TPM desc
limit 10;
(this may or may not work/parse properly, as i didn't actually try it, YMMV,
etc.etc.etc.)
-ravi.
-----Original Message-----
From: Chad Day [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 23, 2001 10:59 AM
To: [EMAIL PROTECTED]
Subject: Help with a semi-complex select statement..
I have 4 tables, named:
players - stores player id, name, team id
teams - teamid, teamname
goals - goals scored, player id, team id
penalty - penaltys accumulated, player id
What I'm trying to do is write a query to get the top 10 from any category
and display them. I can do them individually fine, like so:
SELECT players.PLAYERID, players.FIRSTNAME, players.LASTNAME,
SUM(penalty.PENALTYMINUTES) as TTPM FROM players, penalty where
(penalty.PLAYERID = players.PLAYERID) GROUP BY PLAYERID ORDER BY TTPM DESC
LIMIT 10
but when I try to get the goal total as well or something and add another
table to the from statement, I appear to be getting too many rows back as
the numbers are inflated about 10 times what they should be .. the query I'm
working with is:
SELECT teams.TEAMNAME, players.PLAYERID, players.FIRSTNAME,
players.LASTNAME, SUM(penalty.PENALTYMINUTES) as TPM, COUNT(goals.SCORER) as
TGOALS FROM players, penalty, teams, goals where (penalty.PLAYERID =
players.PLAYERID AND players.TEAMID = teams.TEAMID AND goals.SCORER =
players.PLAYERID AND goals.TEAMID = teams.TEAMID) GROUP BY PLAYERID ORDER BY
TPM DESC LIMIT 10
I'm not sure how I would go about re-writing it to get it to work properly,
and humbly beg for assistance.
Thanks,
Chad
---------------------------------------------------------------------
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
