At 18:15 -0500 3/18/02, John Burns wrote:
>I've looked throught the MySQL documentation and I can't seem to find the
>answer to this problem. I have a query that will work on a Microsoft SQL
>server, but I've tried moving it to a MySQL server and it won't work. I
>tried modifying it and I can't get it to work either. The scenario and
>syntax that I'm trying to use is below. If anyone can help, I'd be
>EXTREMELY GRATEFUL!
What error do you get, exactly? Is it a syntax error? Does the query
execute but return the wrong answer, etc.?
Also, if gametime is present in more than one table, you should qualify
it with a table name.
>
> select a.* b.fieldname, c.teamname as teamname1, d.teamname as teamname2,
>e.teamname as umpire_teamname
> from games AS a
> join fields AS b on a.field = b.fieldid
> join teams AS c on a.team1 = c.teamid
> join teams AS d on a.team2 = d.teamid
> join teams AS e on a.umpire_team = e.teamid
> WHERE gametime > "2001-04-12" and gametime <
>date_add("2001-04-12",interval 7 day)
>
>The scenario is as follows. All of the information I want to grab is in the
>table named "games". Some of the fields in the "games" are using ID #s that
>reference a row in the teams or field tables. I basically want to output
>the data from the "games" table replacing the columns where there is a
>number with the corresponding name from the other table. The columns team1,
>team2 and umpire_team are all numbers and I'm trying to get them to show up
>as the teamname that is listed for that number in the teams table. The
>field column in the games table is a number and should get the fieldname
>from the fields table that corresponds with that number. The where
>statement will really grab based on the current date, but I just have test
>data in the tables right now to try to get the query working. The final
>output should look something like this:
>
>gametime (straight from the games table), team1 (field from the games table,
>joined with the teams table to show the team name), team2 (field from the
>games table, joined with the teams table to show to team name), umpire_team
>(field from the games table, joined with the teams table to show the team
>name), field (field from the games table, joined with the fields table to
>show the field name)
>
>
>---------------------------------------------------------------------
>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
