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!
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
