Jump to content


Photo

Specific Query with Two Tables


  • Please log in to reply
2 replies to this topic

#1 skyace888

skyace888
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 12 October 2006 - 04:19 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 October 2006 - 06:27 PM

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 skyace888

skyace888
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 12 October 2006 - 07:57 PM

Thanks fenway!  I think that did the trick.  I know basic SQL but this one is out of my league!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users