AIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: query requiring two results from one table?
Hi,
Greg's table definitions seem fine te me. Couldn't he just use:
select game.gameid,
game.dateTime,
home.teamID,
home.name,
away.teamID,
away.name
Hi,
Greg's table definitions seem fine te me. Couldn't he just use:
select game.gameid,
game.dateTime,
home.teamID,
home.name,
away.teamID,
away.name
from Games game
left join Teams home on home.teamID = game.homeID
left join Teams away on away.teamID = game.awa
select g.gameid, g.homeid, h.name, g.awayid, a.name, g.datetime
from Games g, Teams h, Teams a
where g.datetime > @min_dt
and g.datetime < @max_dt
and h.teamid = g.homeid
and a.teamid = g.awayid
On Mon, 30 Dec 2002 11:29:45 -0600, <[EMAIL PROTECTED]> wrote:
Hello,
This is my first post to th
IL PROTECTED]>
cc:
Subject: RE: query requiring two results from one table?
Hi,
Your query will need to look like:
select
G.datetime, A.name, B.name
from
Games as G,
Teams as A,
Teams as B
where
A.teamid = G.homeid and
B.teamid = G.awayid and
G.datetime < ?
Your structure is flawed for this kind of query.
Games should be:
Games
---
gameID
===
teamID (FK to teams)
dateTime (datetime)
homeAwayFlag char(1) // either H or A
---
This way you could use something like
select g.gameid,
g.teamID,
g.homeAwayFlag,
g.dateTime,
Hi,
Your query will need to look like:
select
G.datetime, A.name, B.name
from
Games as G,
Teams as A,
Teams as B
where
A.teamid = G.homeid and
B.teamid = G.awayid and
G.datetime < ? and
G.datetime > ?
order by G.datetime
would be a good place to start
Regards
M
-O