Trying to construct a SQL query trouble

Question

Hi all I´m having trouble with creating my desired output with an SQL query. I´m quite new to this and I´m trying to practice. The SQL query first builds the tables for handball matches.

How could I achive this output with a SQL query:

Winner       | GoalsFor | GoalsAgainst | Loser       | GroupID | Venue
---------------------------------------------------------------------------
Austria      | 22       | 22           | South Korea | A       | South Korea
Cuba         | 22       | 22           | Norway      | B       | Norway
Cuba         | 22       | 22           | Norway      | D       | Norway
France       | 19       | 17           | Austria     | A       | Austria
France       | 19       | 17           | Cuba        | D       | Cuba
Iceland      | 23       | 19           | South Korea | A       | Iceland
Iceland      | 30       | 28           | Austria     | A       | Iceland
Iceland      | 37       | 18           | France      | A       | France
Norway       | 23       | 19           | France      | D       | Norway
Russia       | 23       | 19           | Norway      | D       | Russia
Russia       | 30       | 28           | Cuba        | D       | Russia
Russia       | 37       | 18           | France      | D       | France
South Korea  | 23       | 19           | France      | A       | South Korea

With this code:

 create table Teams (
    teamid int primary key,
    country varchar(30) unique not null,
    continent varchar(20) not null,
    strength int not null
)
go

create table Groups (
    groupid char(1) primary key,
    numteams int
)
go

create table GroupsTeams (
    groupid char(1) foreign key references Groups,
    teamid int foreign key references Teams,
    primary key (groupid, teamid)
)
go

create table Games (
   gameid int primary key identity,
   hometeam int not null foreign key references Teams,
   awayteam int not null foreign key references Teams,
   groupid char(1) not null foreign key references Groups,
   homescore int,
   awayscore int,
   unique (hometeam, awayteam, groupid)
)
go

insert into Teams values (1, 'Iceland', 'Europe', 1)
go
insert into Teams values (2, 'South Korea', 'Asia', 2)
go
insert into Teams values (3, 'Austria', 'Europe', 3)
go
insert into Teams values (4, 'France', 'Europe', 4)
go
insert into Teams values (5, 'U.S.', 'America', 5)
go
insert into Teams values (6, 'Norway', 'Europe', 6)
go
insert into Teams values (7, 'Cuba', 'America', 7)
go
insert into Teams values (8, 'Russia', 'Europe', 8)
go 
insert into Teams values (9, 'Egypt', 'Africa', 9)
go

insert into Groups values ('A', 4)
go
insert into Groups values ('B', 4)
go
insert into Groups values ('C', 2)
go
insert into Groups values ('D', 2)
go

insert into GroupsTeams values ('A', 1)
go
insert into GroupsTeams values ('A', 2)
go
insert into GroupsTeams values ('A', 3)
go
insert into GroupsTeams values ('A', 4)
go
insert into GroupsTeams values ('B', 5)
go
insert into GroupsTeams values ('B', 6)
go
insert into GroupsTeams values ('B', 7)
go
insert into GroupsTeams values ('B', 8)
go
insert into GroupsTeams values ('C', 1)
go
insert into GroupsTeams values ('C', 3)
go
insert into GroupsTeams values ('D', 2)
go
insert into GroupsTeams values ('D', 4)
go

insert into Games values (1, 2, 'A', 23, 19)
go
insert into Games values (3, 4, 'A', 17, 19)
go
insert into Games values (1, 3, 'A', 30, 28)
go
insert into Games values (2, 4, 'A', 23, 19)
go
insert into Games values (4, 1, 'A', 18, 37)
go
insert into Games values (2, 3, 'A', 22, 22)
go
insert into Games values (8, 6, 'D', 23, 19)
go
insert into Games values (7, 4, 'D', 17, 19)
go
insert into Games values (8, 7, 'D', 30, 28)
go
insert into Games values (6, 4, 'D', 23, 19)
go
insert into Games values (4, 8, 'D', 18, 37)
go
insert into Games values (6, 7, 'D', 22, 22)
go
insert into Games values (6, 7, 'B', 22, 22)
go

Answer 1

You can use something like this, I included a value if there is a tie of the scores:

select 
  case when g.homescore > g.awayscore
      then ht.country
      when g.awayscore > g.homescore
      then at.country 
      else 'tie' end as winner,
  case when g.homescore > g.awayscore
      then g.homescore
      else  g.awayscore end as GoalsFor,
  case when g.homescore > g.awayscore
      then g.awayscore
      else  g.homescore end as GoalsAgainst,
  case when g.homescore > g.awayscore
      then at.country
      else  ht.country end as Loser,
  g.groupid,
  ht.country Venue
from games g
left join teams ht
  on g.hometeam = ht.teamid
left join teams at
  on g.awayteam = at.teamid
left join groups gp
  on g.groupid = gp.groupid
order by ht.country

See SQL Fiddle with Demo