Hi, I'm new to StackOverflow, and new to SQL Server, I'd like you to help me with some troublesome query.

This is my database structure(It's half spanish, hope doesn't matter)

Database

My problem is that I don't now how to make a query that states which team is local and which is visitor(using table TMatch, knowing that the stadium belongs to only one team)

This is as far as 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 this functions:

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'd explained myself well, and I thank you help in advance