Jump to content


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


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.


Share this post

Link to post
Share on other sites
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'

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.