Jump to content

Archived

This topic is now archived and is closed to further replies.

skyace888

Specific Query with Two Tables

Recommended Posts

I am working on a PHP/MySQL statistics tracking program for a basketball team.  It is something I'm doing for a class.  I have two tables, Play_Log and Play_Outcome_Log, for which I need to run a query on.  Here is what I have so far:

SELECT OutcomeID, Code1 FROM Play_Outcome_Log, Play_Log WHERE Play_Outcome_Log.OutcomeID=5 AND Play_Outcome_Log.PlayID = Play_Log.PlayID AND Play_Log.PlayCode='O'

I am trying to run a query that tells me the player number with the most 2-point baskets on the team (in this example).  The OutcomeID represents the code of the play.  "5" is for a 2-point basket.  Since I want it for the main team, I choose 'O' for PlayCode meaning offensive.  Here is what was returned:

OutcomeID  Code1
5              3
5              3
5              32

I have not added all of the plays in, but at this point, player #3 is the one with the most 2-point baskets.  How can I change the query so I only receive this one number as my result (the player number with the most 2-point baskets)?  Hopefully I have explained this clearly.

Thanks,
Al

Share this post


Link to post
Share on other sites
[code]
SELECT Code1, COUNT(*) AS cnt
FROM Play_Outcome_Log
INNER JOIN Play_Log ON Play_Outcome_Log.PlayID = Play_Log.PlayID
WHERE Play_Outcome_Log.OutcomeID=5 AND Play_Log.PlayCode='O'
GROUP BY Code1 ORDER BY cnt DESC LIMIT 1
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.