two columns but only need to show one query help

[drelefun]

I’m trying to create a sports website to show sports results namely aussie rules football. I want to write a stored procedure to create the results for a separate team results against its opponents for the season. Essentially i have 2 tables
Game: game_id, date, home, away
Score: game_id, team_id, quater, goals, behinds
In the opposition column, I want it to show the opposition team which could be either the home or away team instead of having a home and away column. If its the opposition team’s home game i want to add a @ symbol in front of that result.
I also want the score to start with the team score first and then the oppositions score.

At the end of the day i’d like it to look like

DATE OPPOSITION RESULT MARGIN
25/4/08 Fremantle 101-90 11
2/5/08 @Sydney 88-77 11

I got no idea how to go about it and thought you guys could help.

 

[bborissov]

Could you post some simple data and desired result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[drelefun]

Well i’ve got the two tables data below. The two tables combined would give the result from my previous post. As you can see there is a home and away column but say i’m making the results page for fremantle i want to see the opposition column from my statement produce Richmond, @Sydney, Brisbane, @Geelong. Not too sure how to not show fremantle and just the opposition team and how to add a @ symbol if it was a away team.
Game
Game_id date home Away
1 1/1/08 Fremantle Richmond
2 7/1/08 Sydney Fremantle
3 14/1/08 Fremantle Brisbane
4 21/1/08 Geelong Fremantle

Score
Game_id Team_id quarter goals Behinds
1 Fremantle 1 3 3
1 Fremantle 2 3 1
1 Fremantle 3 2 2
1 Fremantle 4 2 4
1 Richmond 1 5 7
1 Richmond 2 5 4
1 Richmond 3 4 2
1 Richmond 4 6 4

 

[gmmastros]

Try this....

[COLOR=blue]Declare[/color] @TeamName [COLOR=blue]VarChar[/color](20)
[COLOR=blue]Set[/color] @TeamName = [COLOR=red]'Fremantle'[/color]

[COLOR=blue]Select[/color] Score.Game_ID, 
       Game.Date,
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Game.Home = @TeamName [COLOR=blue]Then[/color] Game.Away [COLOR=blue]Else[/color] [COLOR=red]'@'[/color] + Game.Home [COLOR=blue]End[/color] [COLOR=blue]As[/color] Opposition,
       [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Team_ID = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Game.Home = @TeamName [COLOR=blue]Then[/color] Game.Home [COLOR=blue]Else[/color] Game.Away [COLOR=blue]End[/color] [COLOR=blue]Then[/color] Goals [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]))
       + [COLOR=red]'-'[/color] + [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Team_ID = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Game.Home = @TeamName [COLOR=blue]Then[/color] Game.Away [COLOR=blue]Else[/color] Game.Home [COLOR=blue]End[/color] [COLOR=blue]Then[/color] Goals [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color])) [COLOR=blue]As[/color] Result,
       [COLOR=#FF00FF]Abs[/color](Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Team_ID = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Game.Home = @TeamName [COLOR=blue]Then[/color] Game.Home [COLOR=blue]Else[/color] Game.Away [COLOR=blue]End[/color] [COLOR=blue]Then[/color] Goals [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color])-
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] Team_ID = [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Game.Home = @TeamName [COLOR=blue]Then[/color] Game.Away [COLOR=blue]Else[/color] Game.Home [COLOR=blue]End[/color] [COLOR=blue]Then[/color] Goals [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color])) [COLOR=blue]As[/color] Margin
[COLOR=blue]From[/color]   Score
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] Game
         [COLOR=blue]On[/color] Score.Game_ID = Game.Game_ID
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Score.Game_ID, Game.Date,Game.Home, Game.Away

If this works for you, and you would like me to explain any part of it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom