MySQL: How to Combine multiple SELECT queries, using different WHERE criteria on the same table?

Question

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.

I wondered if you could have a look at it for me.

Thanks

THE PROBLEM:

I've got a database with the following tables participant_scores leagues rounds

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:

 Currently viewing league 20, Round 1
 of 2:
 
 User Name |  Score   |  Total Score
 
 Tom       |   10     |    200
 
 James     |   50     |    300

username - the participant's name score = the score for this current round total score = all of the round scores for this league added together.

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.

>> league_participants_query (mysql)

    # 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

Answer 1

What you want here is a subquery in this form:

SELECT
  name,
  round,
  score,
  ( select sum( score ) from scores sc where sc.userid = users.userid ) total
FROM users INNER JOIN scores on users.userid = scores.scoreid

The subquery as a column will be calculated for each row you return from your initial query.

To try to add it into your query:

SELECT
  participants.participant_id,
  participants.participant_name,
  participants.participant_gender,
  classes.class_name,
  schools.school_name,
  participant_scores.participant_score,
  participant_scores.participant_score_id,
  ( SELECT sum(participant_score) FROM participant_scores tbl_scores2
    WHERE tbl_scores2.participant_score_id = participants.participant_id ) total
FROM participant_scores, participants, classes,
  league_schools, schools, leagues, rounds
WHERE
  leagues.league_id = 51  AND
  rounds.league_id = 51  AND
  rounds.round_id = 25 AND
  participant_scores.round_id  = 25  AND
  league_schools.league_id = 51  AND
  schools.school_id = league_schools.school_id  AND
  classes.school_id = schools.school_id  AND
  classes.year_group_id = leagues.year_group_id  AND
  participants.class_id = classes.class_id  AND
  participant_scores.participant_id = participants.participant_id 
GROUP BY
  participants.participant_id

I was a little worried about the subquery including multiple leagues, but it looks like a single participant can only belong to one league anyway. You might need to include something in the subquery to check for this.