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,
h.teamid,
h.name
from Games g left join Teams t on g.teamID = h.teamID
order by dateTime, homeAwayFlag
As it is (IMHO) your only choice is to use 2 SELECT statements and a UNION
(Ugly solution)
select g.gameid,
g.teamID,
g.homeID,
null as awayID,
g.dateTime,
h.teamid,
h.name
from Games g left join Teams t on g.teamID = h.homeID
UNION
select g.gameid,
g.teamID,
null as homeID,
g.awayID,
g.dateTime,
h.teamid,
h.name
from Games g left join Teams t on g.teamID = h.awayID
order by dateTime
I've not tested this but it should point you in the right direction. (But
again, changing your structure is a much better solution.)
HTH,
=C=
*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 30, 2002 11:11 AM
To: [EMAIL PROTECTED]
Subject: query requiring two results from one table?
Hello,
This is my first post to the list, so if I am asking in the wrong place, I
apologize.
I've got some trouble putting together a query with the following tables
Games: Teams:
gameid teamid
homeid name
awayid
datetime
i want to get all games within a certain timeframe, but also retrieve the
team names for both the homeid and awayid (these are both links to the
teams.teamid field).
Not sure how to get two results from the same table in one query.
Can anybody point me in the right direction.
thanks
gf
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
