Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Antonio Conte

Creating Views With Changeble Variables/Similar?

Recommended Posts

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. :)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...