Jump to content


Photo

Need help with WHERE statement


  • Please log in to reply
4 replies to this topic

#1 Phailak

Phailak
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 16 May 2006 - 02:35 PM

Hail,

I have this query I'm building for my hockey league website, so far I'm getting prety much what I want but for some reason I can't narrow it down. Basically, the query gives me the stats of the goalies in the league, in this case some of the goalies returned have 0 games played (because they played in previous seasons but not current one) so I simply want to add to my WHERE statement GP > 0 (I also tried the long version of sum(if (ResultQuery.result="W", 1, 0))+sum(if (ResultQuery.result="L", 1, 0))+sum(if (ResultQuery.result="OTL", 1, 0))+sum(if (ResultQuery.result="T", 1, 0)) > 0) which doesn't work. It sounds like a simple thing I'm missing so I'm hoping someone here can point it out (the WHERE I'm targetting is in red, I want only records which are > than 0):

SELECT gamesplayed.PlayerID, players.PlayerName,
[!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]sum(if (ResultQuery.result="W", 1, 0))+sum(if (ResultQuery.result="L", 1, 0))+sum(if (ResultQuery.result="OTL", 1, 0))+sum(if (ResultQuery.result="T", 1, 0)) as GP[!--colorc--][/span][!--/colorc--],
sum(if (ResultQuery.result="W", 1, 0)) as Wins,
sum(if (ResultQuery.result="L", 1, 0)) as Losses,
sum(if (ResultQuery.result="OTL", 1, 0)) as OTL,
sum(if (ResultQuery.result="T", 1, 0)) as Ties,
sum(ResultQuery.TGA) as GA
FROM gamesplayed INNER JOIN players ON gamesplayed.PlayerID = players.PlayerID
LEFT JOIN (SELECT calendar.CalendarID,
if(teams.TeamID=calendar.HomeTeam, calendar.HomeTeam, calendar.AwayTeam) as Team,
if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals) as TeamGoals,
if(teams.TeamID=calendar.AwayTeam, calendar.HomeTeam, calendar.AwayTeam) as OppTeam,
if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) as OppTeamGoals,
(if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) - if(isnull(OT.OTgoals),0,OT.OTgoals)) as TGA,
OT.OT,
if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)>if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals),"W",if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)=if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals),"T",if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)<if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) AND OT.OT=1, "OTL","L"))) as Result
FROM teams INNER JOIN calendar ON teams.TeamID = calendar.HomeTeam OR teams.TeamID = calendar.AwayTeam
LEFT JOIN (SELECT goals.GameID, goals.TeamID, count(goals.GameID) as Goals FROM goals GROUP BY goals.GameID, goals.TeamID)
AS HomeGoalQuery ON calendar.CalendarID = HomeGoalQuery.GameID AND teams.TeamID= HomeGoalQuery.TeamID
LEFT JOIN (SELECT goals.GameID, goals.OppTeamID, count(goals.GameID) as Goals FROM goals GROUP BY goals.GameID, goals.TeamID)
AS AwayGoalQuery ON calendar.CalendarID = AwayGoalQuery.GameID AND teams.TeamID = AwayGoalQuery.OppTeamID
LEFT JOIN (SELECT calendar.CalendarID, count(calendar.CalendarID) as OTgoals, '1' as OT FROM calendar
INNER JOIN goals on calendar.CalendarID = goals.GameID WHERE Period = 4 GROUP BY calendar.CalendarID) as OT ON calendar.CalendarID = OT.CalendarID
WHERE calendar.Season = 3 AND calendar.Playoffs = 0 AND Cancellation = 0 AND teams.TeamID <> 17 AND teams.TeamID <> 18 ORDER BY teams.TeamID, calendar.CalendarID) AS ResultQuery ON gamesplayed.GameID = ResultQuery.CalendarID AND gamesplayed.TeamID = ResultQuery.Team
WHERE players.Position = "G"
GROUP BY gamesplayed.PlayerID

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 16 May 2006 - 04:03 PM

WHERE players.Position = "G"
GROUP BY gamesplayed.PlayerID
HAVING GP > 0
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]SELECT SYNTAX[/a] (Do a "find" throughout the text for instances of "HAVING")

#3 Phailak

Phailak
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 16 May 2006 - 05:32 PM

[!--quoteo(post=374316:date=May 16 2006, 12:03 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 16 2006, 12:03 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
WHERE players.Position = "G"
GROUP BY gamesplayed.PlayerID
HAVING GP > 0
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]SELECT SYNTAX[/a] (Do a "find" throughout the text for instances of "HAVING")
[/quote]

Yep, that worked, thanks!

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 May 2006 - 07:53 PM

I don't even want to guess how slow that query might be... btw, you should be using IFNULL(), instead of and if() with an isnull() inside. It's cleaner, easier and read, and the parser doesn't have to guess.

e.g.

ifnull(HomeGoalQuery.Goals,0)
instead of:

if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Phailak

Phailak
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 16 May 2006 - 07:58 PM

[!--quoteo(post=374406:date=May 16 2006, 03:53 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 16 2006, 03:53 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I don't even want to guess how slow that query might be... btw, you should be using IFNULL(), instead of and if() with an isnull() inside. It's cleaner, easier and read, and the parser doesn't have to guess.

e.g.

ifnull(HomeGoalQuery.Goals,0)
instead of:

if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)
[/quote]

Oh, well this query is from trial and error on my part so it might look very complicated for no reason, thanks for the suggestion, although the query itself is quite fast, considering the amount of data is pretty low, I guess I don't see the effects of any slowdowns. Still anything I can do to make it clearer is appreciated




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users