Golf Results. CASE and sub-query required? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Golf Results. CASE and sub-query required?

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-11-08 : 07:57:51

I have a database of golf stats from which I wish to extract results for a given player. The matches may have been played either with a partner in foursomes or as a singles match.

My desired endproduct would look something like:-
Match 1 Woods and Mickelson lost to Clarke and Westwood
Match 2 Woods defeated Riley
etc.

Stripped to essentials, I have three tables involved

tblPlayers
-----------
Fields playerID, teamID
Woods,US
Mickelson,US
Clarke, EUR
Westwood,EUR
Casey,EUR

tblPlayerMatch
--------------
Fields playerID,matchID
Woods,1
Mickeson,1
Clarke, 1
Westwood,1
Woods,2
Casey,2

tblResults
----------
Fields matchID,winner
1,EUR
2,US

winner will either = teamID or be 'Halved"
I have managed to get a list of the players involved in each match but
not arrived at the end-product

Any help much appreciated

Andrew Clark
www.majorleaguecharts.com

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-08 : 09:28:35

Depending on your business needs, I see some potential issues with your current schema.
Let me give an example, which may or may not occur in your domain model:

2 US, or EUR players are playing against each other (ie. Woods vs Michelson) how would you indicate the winner?

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-11-08 : 10:22:47

Sorry I should have made this clearer
It is a team competition (actually the Ryder Cup in this example) so it is always US v EUR players
and a player can only ever be on one team even in different years

Andrew Clark
www.majorleaguecharts.com

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-08 : 13:58:32

[code]declare @Players table(PlayerID varchar(20),teamID varchar(20))
declare @PlayerMatch table(PlayerID varchar(20),matchID int)
declare @MatchWinner table(matchID int,winner varchar(20))
insert @Players
select 'Woods','US' union
select 'Mickelson','US' union
select 'Clarke', 'EUR' union
select 'Westwood', 'EUR' union
select 'Casey','EUR'

insert @PlayerMatch
select 'Woods',1 union
select 'Mickelson',1 union
select 'Clarke', 1 union
select 'Westwood',1 union
select 'Woods',2 union
select 'Casey',2

insert @MatchWinner
select 1,'EUR' union
select 2,'US'

select 'Match ' + cast(a.matchid as varchar(10))
,b.players + ' lost to ' + a.players
from
(select mw.matchid
,case when min(pm.PlayerID)= max(pm.PlayerID)
then min(pm.PlayerID)
else min(pm.PlayerID) + '+' + max(pm.PlayerID) end Players
,case when mw.winner=p.teamID then 'w' else 'l' end r
from @MatchWinner mw
join @PlayerMatch pm on mw.matchid=pm.matchid
join @Players p on pm.playerid=p.playerid
group by mw.matchid,case when mw.winner=p.teamID then 'w' else 'l' end
) a
join
(select mw.matchid
,case when min(pm.PlayerID)= max(pm.PlayerID)
then min(pm.PlayerID)
else min(pm.PlayerID) + '+' + max(pm.PlayerID) end Players
,case when mw.winner=p.teamID then 'w' else 'l' end r
from @MatchWinner mw
join @PlayerMatch pm on mw.matchid=pm.matchid
join @Players p on pm.playerid=p.playerid
group by mw.matchid,case when mw.winner=p.teamID then 'w' else 'l' end
) b
on a.matchid=b.matchid and
a.r='w' and b.r='l'[/code]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-08 : 14:24:46

[code]
create function dbo.getPlayers ( @matchid int, @teamid varchar(10) )
returns varchar(100)
as
begin
declare @players varchar(100); set @players = ''

select @players = @players + p.playerid + ' and '
from tblplayers p
join tblplayermatch pm on pm.playerid = p.playerid
and pm.matchid = @matchid
and p.teamid = @teamid

return left(@players,len(@players)-3)
end
go

select
'Match ' + convert(varchar(2),d1.matchid) + ' '+ d1.players + d1.status + d2.players as report,
d1.matchid,
d1.teamid,
d1.players,
d1.status,
d2.teamid,
d2.players,
d2.status
from
(
select
distinct pm.matchid,teamid,winner, dbo.getPlayers(pm.matchid,teamid) players,
case when teamid = winner then 'Defeated '
when teamid != winner and winner != 'Halved' then 'Lost to '
else 'Halved ' end as status
from tblplayermatch pm
join tblplayers p on p.playerid = pm.playerid
join tblresults r on r.matchid = pm.matchid
) d1

join

(
select
distinct pm.matchid,teamid,winner, dbo.getPlayers(pm.matchid,teamid) players,
case when teamid = winner then 'Defeated '
when teamid != winner and winner != 'Halved' then 'Lost to '
else 'Halved ' end as status
from tblplayermatch pm
join tblplayers p on p.playerid = pm.playerid
join tblresults r on r.matchid = pm.matchid
) d2 on d2.matchid = d1.matchid and d2.teamid <> d1.teamid

drop function dbo.getPlayers [/code]

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-11-09 : 13:58:44

Thanks very much for the suggestions guys
ehorn, I had a Invalid object name 'rc_MatchPlayer' (your tblplayermatch) when I adapted it to my tables - not sure why
so I adapted VIGS version utilizing the actual database names and data as follows


select 'Match ' + cast(a.matchid as varchar(10))
       ,case when a.r='d' then a.players + ' halved with ' +b.players
	else a.players + ' defeated ' + b.players end
from
   (select mw.matchid
           ,case when min(p.FirstName+ ' '+p.LastName)= max(p.FirstName+ ' '+p.LastName)
                 then min(p.FirstName+ ' '+p.LastName) 
                 else min(p.FirstName+ ' '+p.LastName) + ' & ' + max(p.FirstName+ ' '+p.LastName) end Players
           ,case when mw.winner=p.team then 'w'
		when mw.winner != p.team and mw.winner != 'Halved' then 'l'
		 else 'd' end  r
    from rc_Matches mw
    join rc_MatchPlayer pm on mw.matchid=pm.matchid
    join rc_Players p on p.PlayerID=pm.playerid 


    group by mw.matchid,case when mw.winner=p.team then 'w'
		when mw.winner != p.team and mw.winner != 'Halved' then 'l'
		 else 'd' end
   ) a
join 
   (select mw.matchid
           ,case when min(p.FirstName+ ' '+p.LastName)= max(p.FirstName+ ' '+p.LastName)
                 then min(p.FirstName+ ' '+p.LastName) 
                 else min(p.FirstName+ ' '+p.LastName) + ' & ' + max(p.FirstName+ ' '+p.LastName) end Players
          ,case when mw.winner=p.team then 'w'
		when mw.winner != p.team and mw.winner != 'Halved' then 'l'
		 else 'd' end  r
    from rc_Matches mw
    join rc_MatchPlayer pm on mw.matchid=pm.matchid
    join rc_Players p on p.PlayerID=pm.playerid 
    group by mw.matchid,case when mw.winner=p.team then 'w'
		when mw.winner != p.team and mw.winner != 'Halved' then 'l'
		 else 'd' end

   ) b
on (a.matchid=b.matchid and 
   (a.r='d' and b.r='d')) OR (a.matchid=b.matchid and 
   (a.r='w' and b.r='l'))

The only problem with this is that if it is a singles match I get a duplication of the players
e.g. Frank Beard & Neil Coles halved with Frank Beard & Neil Coles

I also want to filter to get an individual's results only, based on his playerID which
is in both rc_Players and rc_MatchPlayer but have not solved this either

Andrew Clark
www.majorleaguecharts.com

AndyC
Yak Posting Veteran

53 Posts

Posted - 2004-11-11 : 17:43:18

I have now solved the 'Halved' problem and also am able to filter by
year viz



select 'Match ' + cast(a.matchid as varchar(10))
       ,case when a.r='d' then a.players + ' halved with ' 

+b.players
	else a.players + ' defeated ' + b.players end
from
   (select mw.matchid,p.team,
          case when min(p.FirstName+ ' '+p.LastName)= 

max(p.FirstName+ ' '+p.LastName)
                 then min(p.FirstName+ ' '+p.LastName) 
                 else min(p.FirstName+ ' '+p.LastName) + ' & 

' + max(p.FirstName+ ' '+p.LastName) end Players
           ,case when mw.winner=p.team then 'w'
		when mw.winner != p.team and mw.winner != 

'Halved' then 'l'
		 else 'd' end  r
    from rc_Matches mw
    join rc_MatchPlayer pm on mw.matchid=pm.matchid
    join rc_Players p on p.PlayerID=pm.playerid 
	where mw.year='2004'

    group by mw.matchid,p.team,case when mw.winner=p.team 

then 'w'
		when mw.winner != p.team and mw.winner != 

'Halved' then 'l'
		 else 'd' end
   ) a
join 
   (select mw.matchid,p.team
           ,case when min(p.FirstName+ ' '+p.LastName)= 

max(p.FirstName+ ' '+p.LastName)
                 then min(p.FirstName+ ' '+p.LastName) 
                 else min(p.FirstName+ ' '+p.LastName) + ' & 

' + max(p.FirstName+ ' '+p.LastName) end Players
          ,case when mw.winner=p.team then 'w'
		when mw.winner != p.team and mw.winner != 

'Halved' then 'l'
		 else 'd' end  r
    from rc_Matches mw
    join rc_MatchPlayer pm on mw.matchid=pm.matchid
    join rc_Players p on p.PlayerID=pm.playerid 
where left(mw.matchID,4)='2004'
    group by mw.matchid,p.team,case when mw.winner=p.team 

then 'w'
		when mw.winner != p.team and mw.winner != 

'Halved' then 'l'
		 else 'd' end

   ) b
on (a.matchid=b.matchid and 
   (a.r='d' and b.r='d') and a.team<>b.team and a.team='EUR') 

OR (a.matchid=b.matchid and 
   (a.r='w' and b.r='l'))

but am still failing to filter for the playerID
Any suggestions?

Andrew Clark
www.majorleaguecharts.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources