Problem with SQL query

Question

I am new to StackOverflow and new to SQL Server, I would like you to help me with some nasty query.

This is my database structure (it is half Spanish, hope does not matter)

Database

My problem is that I am not now making a request that indicates which team is local and which is the visitor (using the TMatch table, knowing that the stadium belongs to only one team)

This is how much I can get

Select P.NroMatch, (select * from fnTeam (P.TeamA)) as TeamA,(select * from fnTeam (P.TeamB)) as TeamB,
    (select * from fnEstadium (P.CodEstadium)) as Estadium, (cast(P.GolesTeamA as varchar)) + '-' + (cast(P.GolesTeamA as varchar)) as Score,
    P.Fecha
from TMatch P

Using these features:

If object_id ('fnTeam','fn')is not null
drop function fnTeam
go
create function fnTeam(@CodTeam varchar(5))
returns table 

return(Select Name from TTeam where CodTeam = @CodTeam)
go

select * from fnTeam ('Eq001')
go
----****
If object_id ('fnEstadium','fn')is not null
drop function fnEstadium
go
create function fnEstadium(@CodEstadium varchar(5))
returns table 

return(Select Name from TEstadium where CodEstadium = @CodEstadium)
go

I hope I would explain myself well, and I thank you in advance for your help

EDIT:

Thanks for the help, this is what I was looking for

Select P.NroMatch, 
CASE 
    WHEN Ts.CodTeam= Ta.CodTeamTHEN Ta.Name
    ELSE Tb.Name
END 
As TeamLocal, 
CASE 
    WHEN Ts.CodTeam<> Ta.CodTeamTHEN Ta.Name
    ELSE Tb.Name
END 
As TeamVisitante,
Ts.Name as Estadium, 
(cast(P.GolesTeamA as varchar)) + '-' + (cast(P.GolesTeamB as varchar)) as Score,     
P.Fecha
from 
   TMatch P 
   join TTeamTa ON Ta.CodTeam= P.TeamA
   join TTeamTb ON Tb.CodTeam= P.TeamB
   join TEstadium Ts ON Ts.CodEstadium = P.CodEstadium  

Answer 1

"lookup" ( ), - :

Select 
P.NroMatch, 
Ta.Name as TeamA,
Tb.Name as TeamB,     
Ts.Name as Estadium, 
cast(P.GolesTeamA as varchar)) + '-' + (cast(P.GolesTeamA as varchar) as Score,     
P.Fecha,
CASE 
    WHEN Ts.CodTeam = Ta.Name THEN Ta.Name
    ELSE Tb.Name
END As HomeTeam,
CASE 
    WHEN Ts.CodTeam <> Ta.Name THEN Ta.Name 
    ELSE Tb.Name 
 END As VistorTeam
from 
   TMatch P 
   join TTeam Ta ON Ta.CodTeam = P.TeamA
   join TTeam Tb ON Tb.CodTeam = P.TeamB
   join TEstadium Ts ON Ts.CodEstadium = P.CodEstadium

SQL, SO , :

SQL

SQL