?
Solved

trouble with query - part 2 (or 6 :) )

Posted on 2012-09-09
7
Medium Priority
?
357 Views
Last Modified: 2013-02-14
this is a follow up to the following question:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27791672.html

at this point, everything is working as is, except if a team has not played any games, their records of 0-0-0 will not show up. I've tried changing the inner joins to tTeams to left outer joins, but that didn't work either.

here's my current query:

select T.Team, T.Color, isnull(P.firstname,'Captain') + ' ' + isnull(P.lastname,'Unknown') as Captain
       , sum(wins) as Wins
       , sum(ties) as Ties
       , sum(losses) as Losses
       , sum( wins + Ties + Losses ) as GamesPlayed
       , sum(GoalsFor) as GoalsFor
       , sum(GoalsAgainst) as GoalsAgainst
       , sum(case when OT = 'N' and wins = 1 then 1 else 0 end) as RegulationWins
       , sum(case when OT = 'Y' and wins = 1 then 1 else 0 end) as OTWins
       , sum(case when OT = 'Y' and ties = 1 then 1 else 0 end) as OTTies
       , sum(case when OT = 'Y' and losses = 1 then 1 else 0 end) as OTLosses
       , sum(case when divisional = 'Y' and wins = 1 then 1 else 0 end) as DivisionalWins  
       , sum(case when divisional = 'Y' and Ties = 1 then 1 else 0 end) as DivisionalTies
       , sum(case when divisional = 'Y' and losses = 1 then 1 else 0 end) as DivisionalLosses 
	   , sum(case when OT = 'N' and wins = 1 then 3
                  when OT = 'Y' and wins = 1 then 2
                  when OT = 'Y' and losses = 1 then 1
             else 0 end) as points 

from 
    ( select G.Gameid, 'H' as home_away, Hometeam as teamid, homescore as GoalsFor, awayscore as GoalsAgainst
             , case when homescore > awayscore then 1 else 0 end as wins 
             , case when homescore = awayscore then 1 else 0 end as ties
             , case when homescore < awayscore then 1 else 0 end as losses 
             , case when h.divisionid=a.divisionid and h.divisionid = @divisionID then 'Y' else 'N' end as divisional 
             , h.divisionid
             , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT
      from   tGames G
      inner join tteams as H on g.hometeam = h.teamid
      inner join tteams as A on g.awayteam = a.teamid
      where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID )

      union all

      select G.Gameid, 'A' as home_away, Awayteam as teamid, awayscore as GoalsFor, homescore as GoalsAgainst
             , case when homescore < awayscore then 1 else 0 end as wins
             , case when homescore = awayscore then 1 else 0 end as ties
             , case when homescore > awayscore then 1 else 0 end as losses 
             , case when h.divisionid=a.divisionid and a.divisionid = @divisionID then 'Y' else 'N' end as divisional
             , a.divisionid
             , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT  
      from   tGames G
      inner join tteams as H on g.hometeam = h.teamid
      inner join tteams as A on g.awayteam = a.teamid
      where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID )
    ) games 
    inner join tTeams T on games.teamid = T.teamid
    left outer join tPlayers P on T.captainid = P.playerid
	where t.divisionID = @divisionID 

Group By T.Team, T.Color, isnull(P.firstname,'Captain'), isnull(P.lastname,'Unknown')
Order By points DESC

Open in new window


thanks in advance!
0
Comment
Question by:Big Monty
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38380887
try changing the join to
     from   tteams as H
     left join tGames G on g.hometeam = h.teamid
     left join tteams as A on g.awayteam = a.teamid

and for the away side of the union all

     from   tteams as A
     left join tGames G on g.hometeam = a.teamid
     left join tteams as H on g.awayteam = h.teamid

Open in new window

0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38380889
actually ... another idea is
select T.Team, T.Color, isnull(P.firstname,'Captain') + ' ' + isnull(P.lastname,'Unknown') as Captain
       , sum(wins) as Wins
       , sum(ties) as Ties
       , sum(losses) as Losses
       , sum( wins + Ties + Losses ) as GamesPlayed
       , sum(GoalsFor) as GoalsFor
       , sum(GoalsAgainst) as GoalsAgainst
       , sum(case when OT = 'N' and wins = 1 then 1 else 0 end) as RegulationWins
       , sum(case when OT = 'Y' and wins = 1 then 1 else 0 end) as OTWins
       , sum(case when OT = 'Y' and ties = 1 then 1 else 0 end) as OTTies
       , sum(case when OT = 'Y' and losses = 1 then 1 else 0 end) as OTLosses
       , sum(case when divisional = 'Y' and wins = 1 then 1 else 0 end) as DivisionalWins  
       , sum(case when divisional = 'Y' and Ties = 1 then 1 else 0 end) as DivisionalTies
       , sum(case when divisional = 'Y' and losses = 1 then 1 else 0 end) as DivisionalLosses 
	   , sum(case when OT = 'N' and wins = 1 then 3
                  when OT = 'Y' and wins = 1 then 2
                  when OT = 'Y' and losses = 1 then 1
             else 0 end) as points 

from 
    tTeams T
	left outer join
    ( select G.Gameid, 'H' as home_away, Hometeam as teamid, homescore as GoalsFor, awayscore as GoalsAgainst
             , case when homescore > awayscore then 1 else 0 end as wins 
             , case when homescore = awayscore then 1 else 0 end as ties
             , case when homescore < awayscore then 1 else 0 end as losses 
             , case when h.divisionid=a.divisionid and h.divisionid = @divisionID then 'Y' else 'N' end as divisional 
             , h.divisionid
             , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT
      from   tGames G
      inner join tteams as H on g.hometeam = h.teamid
      inner join tteams as A on g.awayteam = a.teamid
      where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID )

      union all

      select G.Gameid, 'A' as home_away, Awayteam as teamid, awayscore as GoalsFor, homescore as GoalsAgainst
             , case when homescore < awayscore then 1 else 0 end as wins
             , case when homescore = awayscore then 1 else 0 end as ties
             , case when homescore > awayscore then 1 else 0 end as losses 
             , case when h.divisionid=a.divisionid and a.divisionid = @divisionID then 'Y' else 'N' end as divisional
             , a.divisionid
             , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT  
      from   tGames G
      inner join tteams as H on g.hometeam = h.teamid
      inner join tteams as A on g.awayteam = a.teamid
      where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID )
    ) games  on games.teamid = T.teamid
    left outer join tPlayers P on T.captainid = P.playerid
	where t.divisionID = @divisionID 

Group By T.Team, T.Color, isnull(P.firstname,'Captain'), isnull(P.lastname,'Unknown')
Order By points DESC

Open in new window

0
 
LVL 34

Author Comment

by:Big Monty
ID: 38380940
that did it! thanks a bunch :)

I'm trying to figure out what exactly you changed, would you mind telling me?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 38381261
*laughing* looks like I am too late to the party :)

The change was adding in a new join to that union subquery (with the alias name of "games"), so, select from teams and then left outer join to the "games" subquery rather than the games subquery doing an inner join to tteams.

Your other choice was to make that inner join to tteams a "right outer join" to tteams (meaning include everything in tteams even if it doesnt match the join criteria). And notice the use of ISNULL in the select to make sure you get the zero's that you asked for ...

e.g.

declare @gametype varchar(20) = 'regular'
declare @season int = 2
declare @divisionid int = 3

select T.Team, T.Color, isnull(P.firstname,'Captain') + ' ' + isnull(P.lastname,'Unknown') as Captain
       , sum(isnull(wins,0)) as Wins
       , sum(isnull(ties,0)) as Ties
       , sum(isnull(losses,0)) as Losses
       , sum(isnull( (wins + Ties + Losses),0) ) as GamesPlayed
       , sum(isnull(GoalsFor,0)) as GoalsFor
       , sum(isnull(GoalsAgainst,0)) as GoalsAgainst
       , sum(case when OT = 'N' and wins = 1 then 1 else 0 end) as RegulationWins
       , sum(case when OT = 'Y' and wins = 1 then 1 else 0 end) as OTWins
       , sum(case when OT = 'Y' and ties = 1 then 1 else 0 end) as OTTies
       , sum(case when OT = 'Y' and losses = 1 then 1 else 0 end) as OTLosses
       , sum(case when divisional = 'Y' and wins = 1 then 1 else 0 end) as DivisionalWins  
       , sum(case when divisional = 'Y' and Ties = 1 then 1 else 0 end) as DivisionalTies
       , sum(case when divisional = 'Y' and losses = 1 then 1 else 0 end) as DivisionalLosses 
	   , sum(case when OT = 'N' and wins = 1 then 3
                  when OT = 'Y' and wins = 1 then 2
                  when OT = 'Y' and losses = 1 then 1
             else 0 end) as points 

from 
    ( select G.Gameid, 'H' as home_away, Hometeam as teamid, homescore as GoalsFor, awayscore as GoalsAgainst
             , case when homescore > awayscore then 1 else 0 end as wins 
             , case when homescore = awayscore then 1 else 0 end as ties
             , case when homescore < awayscore then 1 else 0 end as losses 
             , case when h.divisionid=a.divisionid and h.divisionid = @divisionID then 'Y' else 'N' end as divisional 
             , h.divisionid
             , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT
      from   tGames G
      inner join tteams as H on g.hometeam = h.teamid
      inner join tteams as A on g.awayteam = a.teamid
      where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID )

      union all

      select G.Gameid, 'A' as home_away, Awayteam as teamid, awayscore as GoalsFor, homescore as GoalsAgainst
             , case when homescore < awayscore then 1 else 0 end as wins
             , case when homescore = awayscore then 1 else 0 end as ties
             , case when homescore > awayscore then 1 else 0 end as losses 
             , case when h.divisionid=a.divisionid and a.divisionid = @divisionID then 'Y' else 'N' end as divisional
             , a.divisionid
             , isnull((select 'Y' from tplayerstats ps where ps.gameid = g.gameid and ps.period = 4),'N') as OT  
      from   tGames G
      inner join tteams as H on g.hometeam = h.teamid
      inner join tteams as A on g.awayteam = a.teamid
      where g.season = @season and g.gameType = @gameType and ( h.divisionID = @divisionID OR a.divisionID = @divisionID )
    ) games 
    right outer join tTeams T on games.teamid = T.teamid
    left outer join tPlayers P on T.captainid = P.playerid
	where t.divisionID = @divisionID 

Group By T.Team, T.Color, isnull(P.firstname,'Captain'), isnull(P.lastname,'Unknown')
Order By points DESC

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38381319
I moved the "inner join tTeams T on games.teamid = T.teamid" on line 47 of your original query to above the inline view so that the "tTeams T" so that the table would be on the left when 'left join'ed.
As @mark_wills pointed out ... it could have remained where it was and a 'right join' done.  I preferred to move it as you already had a left join from it, purely as a style preference of mine, so that it would be two left join's.
0
 
LVL 34

Author Comment

by:Big Monty
ID: 38383189
great, thanks guys for the explanation :)
0
 
LVL 34

Author Comment

by:Big Monty
ID: 38889896
hey there I have a follow up to this question, any chance you could help it would be greatly appreciated!

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28031210.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question