Yes, this is what i ended up doing and it works just fine. I wanted the
results in one record
and this does the job just fine.
I don't know enough about performance issues and whether this kind of
query
is inefficient compared to using a different database layout, but this is
not a
highly intensive application and i'm developing on an old PII-200 and the
speed
of the results is negligible, so I am very happy with this solution.
gf
Fred van Engen <[EMAIL PROTECTED]>
01/02/2003 10:57 AM
To: Cal Evans <[EMAIL PROTECTED]>
cc: [EMAIL 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
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
