August 12, 2004 at 9:01 am
Hi all. I'm very new to sql and I am having some problems getting the results I want. Here are the tables I'm dealing with.
Teams
--------
team_id (PK)
team_name
Schedule
-----------
game_id (PK)
home_team_id (team_id)
away_team_id (team_id)
I'm trying to write a sql statement that returns the home team name and the away team name from the teams table, but I'm not sure how to do it. I assumed I would have to use an inner join some how, but I can't get it to work.
Any help would be appreciated
August 12, 2004 at 9:44 am
This should work
select c.game_id, a.team_name as home_team, b.team_name as away_team
from dbo.teams a
inner join dbo.teams b on 1 = 1
inner join dbo.schedule c on c.home_team_id = a.team_id and c.away_team_id = b.team_id
/hm
August 12, 2004 at 9:52 am
if exists (Select * from dbo.sysobjects where name ='Teams' and xtype = 'U')
drop table Teams
create table Teams(team_id int IDENTITY (1, 1) not null primary key, team_name varchar(50) not null)
if exists (Select * from dbo.sysobjects where name ='Schedule' and xtype = 'U')
drop table Schedule
create table Schedule (game_id int IDENTITY (1, 1) not null primary key, home_team_id int not null, away_team_id int not null)
GO
Insert into Teams (team_name) values ('Canadians')
Insert into Teams (team_name) values ('Bruins')
Insert into Schedule (home_team_id, away_team_id) (Select (Select team_id from Teams where team_name = 'Canadians') as home_team_id, (Select team_id from Teams where team_name = 'Bruins') as away_team_id)
SELECT dbo.Schedule.game_id, Home.team_name AS HomeTeam, Away.team_name AS AwayTeam
FROM dbo.Schedule INNER JOIN
dbo.Teams Home ON dbo.Schedule.home_team_id = Home.team_id INNER JOIN
dbo.Teams Away ON dbo.Schedule.away_team_id = Away.team_id
August 12, 2004 at 10:48 am
That works wonderfully. Thank you.
August 13, 2004 at 8:09 am
Hi,
declare @vchHomeTeam varchar(50),
@vchAwayTeam varchar(50),
@intGameId integer
select @intGameId = 1
select
@vchHomeTeam = team_name
from
Teams,
Schedule
where
team_id = home_team_id
and game_id = @intGameId
select
@vchAwayTeam = team_name
from
Teams,
Schedule
where
team_id = away_team_id
and game_id = @intGameId
select @vchHomeTeam 'HomeTeam'
select @vchAwayTeam 'AwayTeam'
August 18, 2004 at 12:10 am
just paste this statement on your query analyzer it will show what u need !!!
SELECT B.GAME_ID,A.TEAM_NAME ,C.TEAM_NAME FROM Teams A,Teams C,Schedule B WHERE
B.home_team_id=A.TEAM_ID AND
B.AWAY_team_id=C.TEAM_ID
Have a nice time ....
- Manish
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply