title: MySQL: How to Combine multiple SELECT queries, using different WHERE criteria on the same table?
body: <p>I have been trying many different ways to solve this problem from this forum and from many others. I can't seem to find a solution to this problem or any documentation that will give me a straight answer.</p>
  <p>I wondered if you could have a look at it for me.</p>
  <p>Thanks</p>
  <p><strong>THE PROBLEM:</strong></p>
  <p>I've got a database with the following tables
  participant_scores
  leagues
  rounds</p>
  <p>I am currently able to display the scores of a single round, one round at a time... which is exactly how I want it.
  But I also want to display the score that each participant got for all rounds.
  Lets say we have 2 rounds. I want the output on my results screen to look something like this:</p>
  <pre><code> Currently viewing league 20, Round 1
   of 2:
   
   User Name |  Score   |  Total Score
   
   Tom       |   10     |    200
   
   James     |   50     |    300
  </code></pre>
  <p>username - the participant's name
  score = the score for this current round
  total score = all of the round scores for this league added together.</p>
  <p>My Mysql query is below. Apologies for the messyness of it, I've rewritten it about 100 times and this current way is the only way that works fully.</p>
  <h1>&gt;&gt; league_participants_query (mysql)</h1>
  <pre><code>    # FIELDS
      SELECT    
          participants.participant_id,                                          # ID - used for functions
          participants.participant_name,                                        # NAME
          participants.participant_gender,                                      # Participant info             
          classes.class_name,                                                   # Class name
          schools.school_name,                                                  # School name
          participant_scores.participant_score,                                 # Participant score
          participant_scores.participant_score_id
  
  
  
      # TABLES
      FROM         participant_scores, participants, classes, league_schools, schools, leagues, rounds    
  
  
      # filter leagues
      WHERE        leagues.league_id               =     51
  
      AND          rounds.league_id                =     51    # the current league we are viewing
      AND          rounds.round_id                 =     25    # the current round of the league we are viewing
  
      # filter league schools
      AND          participant_scores.round_id     =     25    # the current round of the league we are viewing
  
      # filter schools allowed
      AND          league_schools.league_id        =     51    # the current league we are viewing
  
      # Filter schools
      AND          schools.school_id               =     league_schools.school_id
  
      # Filter classes
      AND          classes.school_id               =     schools.school_id                                        
      AND          classes.year_group_id           =     leagues.year_group_id
  
      # Filter participants
      AND          participants.class_id           =     classes.class_id
  
      # Filter participant_scores
      AND          participant_scores.participant_id   =      participants.participant_id
  
      #Grouping
      GROUP BY     participants.participant_id
  </code></pre>