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

Question

Hello Stack Overflow,

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.

Answer 2

Okay here's my code so far. I realise I'm quite a way off yet. Thanks so much for your help!

>> league_participants_query

                            # 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,   
     participant_scores.participant_score,
     participant_scores.participant_score_id

     SELECT sum(participant_score) 
     FROM  participant_scores tbl_scores2
     WHERE tbl_scores2.participant_id   = participants.particpant_id) total




                            # TABLES
                            FROM             classes, league_schools, schools, leagues, rounds, participants INNER JOIN participant_scores ON participants.participant_id = participant_scores.participant_id                     


                            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

... Error Message: You have an error in your SQL syntax at line 12. Near: "SELECT sum(participant_score)"