Creating Views With Changeble Variables/Similar?

[Antonio Conte]

Hey

 

Note: This query is actually returning the right/correct data. You may skip the initial explanation...

 

I'm currently working on a large query with a lot of subqueries. What I need to do, is to count the total number of games won, defeated and undecided. (by example, a 1-1 score). The count should be based on a specific team and also differ between home and away matches.

 

This is the structure of the result from the query:

Team / home_wins / home_defeats / home_undecided / away_wins / away_defeats / away_undecided

 

What I'm wondering:

 

The problem is that subqueries cannot return more than ONE result. Because of this, the calculations needs to be performed on a per-league-bases. Because I'll still need similar functionality next season, I'm wondering if I could make a function/use a changable variable here. It should be possible to change the league_id for this calculation every year, or even better, find a way to calculate each league of it's own. I just don't think that's possible.

 

Here's the query:

 

SET @league_id = 11;
SET @club_id = 11;

SELECT
games.league_id AS league_id,
league.name AS league,

(  SELECT SUM(goals_club1 > goals_club2)
  FROM cnk_soccer_games
  WHERE club1_id = @club_id AND league_id = @league_id )  AS home_wins,
(  SELECT SUM(goals_club1 = goals_club2)
  FROM cnk_soccer_games
  WHERE club1_id = @club_id AND league_id = @league_id )  AS home_undecided,
(  SELECT SUM(goals_club1 < goals_club2)
  FROM cnk_soccer_games
  WHERE club1_id = @club_id AND league_id = @league_id )  AS home_lost,
(  SELECT SUM(goals_club1 < goals_club2)
  FROM cnk_soccer_games
  WHERE club2_id = @club_id AND league_id = @league_id )  AS away_wins,
(  SELECT SUM(goals_club1 = goals_club2)
  FROM cnk_soccer_games
  WHERE club2_id = @club_id AND league_id = @league_id )  AS away_undecided,
(  SELECT SUM(goals_club1 > goals_club2)
  FROM cnk_soccer_games
  WHERE club2_id = @club_id AND league_id = @league_id )  AS away_lost

FROM cnk_soccer_games AS games
INNER JOIN cnk_soccer_league AS league ON ( games.league_id = league.id )

WHERE ( 
  games.goals_club1 IS NOT NULL OR games.goals_club2 IS NOT NULL 
) 
AND league_id = @league_id

GROUP BY games.league_id

 

An explanation of the query:

- The query is only counting games from league_id 11

- Only counting games where the score is not NULL (the games are already played)

- The results are grouped on a per-league-basis

 

- Each subquery checks the score of club1 to that of club2 (home and away team)

- The result of the sub-queries depends on the comparisons (should the home/away team win?).... And to wheater OUR team is the home or away team

 

Hope someone can help me with this.

[Larry]

I meant to get to this today but ran out of time. I'll come back to it on Monday.

[Antonio Conte]

Just bumping this in case you forgot about it.

[Larry]

Yeah, yeah. Thanks. Monday disappeared, along with Wednesday and most of Friday. Okay, sorry about the delay.

 

So, not a great answer yet, but it seems to me that this is a good occasion to use a VIEW. Anytime I have a complicated query that I'd like to turn into a simple query, I think of a VIEW. Also, you don't explain what your schema is, but it's possible that a change there would make this easier.

 

As for changing this from year to year, that should be quite easy using a variable.