List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 18 2002 11:33pm
Subject:Re: MySQL syntax different from Microsoft SQL syntax...Please HELP
View as plain text  
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 <mysql-thread103353@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
MySQL syntax different from Microsoft SQL syntax...Please HELPJohn Burns19 Mar
  • Re: MySQL syntax different from Microsoft SQL syntax...Please HELPChristopher Thompson19 Mar
  • Re: MySQL syntax different from Microsoft SQL syntax...Please HELPPaul DuBois19 Mar