Simple SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple SQL Query

Author

Topic

sleake
Starting Member

2 Posts

Posted - 2007-11-08 : 10:29:37

I have a teams table with an id col (primark key) and name col.

I also have a fixtures table. its has an Id, hometeam, awayteam and date cols.

I would like to use the Pk of the Teams table as a foreign key in the fixtures table for both the hometeam and away team cols.

How could I write the SQL to get a list of fixtures for a specific team given the ID of the team.

Many thanks for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-08 : 10:32:46

post your table DDL, sample data and required result

KH
[spoiler]Time is always against us[/spoiler]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-08 : 10:35:59

you mean like...

select t1.name as hometeam,t2.name as awayteam,fixture_date
from fixtures
join teams t1 on hometeam = t1.id
join teams t2 on awayteam = t2.id

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-09 : 01:22:18

quote:
Originally posted by elancaster

you mean like...

select t1.name as hometeam,t2.name as awayteam,fixture_date
from fixtures
join teams t1 on hometeam = t1.id
join teams t2 on awayteam = t2.id

Em

Is that correct?

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 02:44:57

quote:

Is that correct?

Madhivanan

i think so...? unless i've misunderstood what they're after...?
they just need to add a where clause eh?


declare @fixture table (home int, away int, dat datetime)

insert into @fixture
select 1,10,'20070101'

declare @teams table (t_id int, t_name varchar(10))

insert into @teams
select 1,'team 1'
union all select 10,'team 2'

declare @team_id int
set @team_id = 1

select t1.t_name as hometeam,t2.t_name as awayteam,dat
from @fixture
join @teams t1 on home = t1.t_id
join @teams t2 on away = t2.t_id
where home = @team_id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-09 : 03:29:59

Well. I just pointed out that you used alias name in joins in your first example

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-09 : 03:47:23

ah... you mean the hometeam / awayteam thing?
i actually meant them as the columns from the fixtures table in the join... then just aliased them in the select for ease of reading. should've used a different name i guess

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources