SQL query problem

Question

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)

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

EDIT:

Thanks for the help, this is what I've been 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

Mitch Wheat · Accepted Answer · 2011-03-07 00:35:33Z

4

You do not need to use 'lookup' functions for this (and shouldn't), joins are a better approach:

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

If you are new to SQL, it might be useful searching SO for some resources such as these:

SQL Tutorial

edited Mar 7, 2011 at 0:35

answered Mar 6, 2011 at 23:49

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

brisonela Over a year ago

sorry, the datasabase is in the link(they don't let new users post images). Also, if TeamA and TeamB are playing in TeamA stadium, teamA is local

brisonela Over a year ago

sorry for being so pushy but the problem is with the "visitor"

Mitch Wheat Over a year ago

@Brisonela: I have no idea what you mean?

Collectives™ on Stack Overflow

SQL query problem

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related