What error message do you get? Have you tried fully spelling out your join
condition's table names? Have you tried specifying that you are doing a LEFT
join? Have you tried using USING instead of ON?
On Monday 18 March 2002 4:15 pm, 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!
>
> 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
