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.awayID Unless he wants the result in two rows of course. Regards, Fred. On Mon, Dec 30, 2002 at 11:37:03AM -0600, Cal Evans wrote: > 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 > -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands --------------------------------------------------------------------- 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