transfering a mysql database/php application to a new server - version troubles

[slushpuppie]

moving a site onto a new server running mysql 4.1.11, where as the old box was running 4.1.22, here's the query that's giving me trouble:

 

SELECT `vc_games`.`id`, `name`, `system`, `year`, `desc`, `release`, `cost`, AVG(`rating`) AS `score` FROM `vc_games` LEFT JOIN `users_ratings` ON `vc_games`.`id` = `users_ratings`.`game_id` RIGHT JOIN `users` `u` ON `u`.`id` = `users_ratings`.`user_id` WHERE 1 AND `u`.`banned` != 'yes' GROUP BY `name`, `system` ORDER BY `name` ASC

 

on the old server (4.1.22) it'd return every game whether or not it had been rated yet (`users_ratings`). it would simply return NULL as the value for `score`. however on this new server (4.1.11) it doesn't return any results where there isn't a rating, so any game that has not been rated isn't showing up in the list. this is the structure of the databases. anyone have an idea of why it isn't working on the new server? is this just a case of it accidentally working in 4.1.22? is there something in 4.1.11 that i need to specify to get it to handle the NULL in `score` differently?

 

CREATE TABLE `vc_games` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(100) NOT NULL DEFAULT '',

  `system` enum('arcade','c64','gen','sms','n64','neogeo','nes','snes','tg16','none') NOT NULL DEFAULT 'none' COMMENT 'rl -> vc_system',

  `year` year(4) NOT NULL DEFAULT '0000',

  `desc` text NOT NULL,

  `release` date NOT NULL DEFAULT '0000-00-00',

  `cost` enum('500','600','700','800','900','1000','1200') NOT NULL DEFAULT '500' COMMENT 'rl -> vc_cost',

  `file` varchar(100) NOT NULL DEFAULT '',

  `ext` varchar(5) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=306 ;

 

CREATE TABLE `users_ratings` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` int(11) unsigned NOT NULL DEFAULT '0',

  `game_id` int(11) unsigned NOT NULL DEFAULT '0',

  `rating` smallint(2) unsigned NOT NULL DEFAULT '0',

  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`id`),

  UNIQUE KEY `user_id` (`user_id`,`game_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

 

CREATE TABLE `users` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `email` varchar(255) NOT NULL DEFAULT '',

  `display_name` varchar(32) NOT NULL DEFAULT '',

  `password` varchar(128) NOT NULL DEFAULT '',

  `about_me` text NOT NULL,

  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `lastlogin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `icon` varchar(255) NOT NULL DEFAULT 'default.jpg',

  `banned` enum('yes','no') NOT NULL DEFAULT 'no',

  PRIMARY KEY (`id`),

  UNIQUE KEY `display_name` (`display_name`),

  UNIQUE KEY `email` (`email`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

 

would appreciate any help - hopefully this data will be useful, let me know if any other info is needed! thanks so much.

[fenway]

Mixing LEFT and RIGHT JOINs makes it very hard to follow.... but that shouldn't be an issue.

 

Hard to say without sample data.  are the JOIN plans the same (from EXPLAIN)?

[slushpuppie]

this is what i get on an EXPLAIN (i apologize if the formatting from this site makes it hard to read):

 

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	u 	ALL 	NULL 	NULL 	NULL 	NULL 	2 	Using where; Using temporary; Using filesort
1 	SIMPLE 	users_ratings 	ref 	user_id 	user_id 	4 	SERVER.u.id 	1 	 
1 	SIMPLE 	vc_games 	eq_ref 	PRIMARY 	PRIMARY 	4 	SERVER.users_ratings.game_id 	1 	 

 

also - i am able to get the query to return the desired results if i remove the RIGHT JOIN ("RIGHT JOIN `users` `u` ON `u`.`id` = `users_ratings`.`user_id`") and of course the WHERE clause that's associated with that... however the query will return ratings for users with banned accounts, which was the reason the RIGHT JOIN is in there.

 

thanks very much for your reply.

[slushpuppie]

was just thinking, a simple textual explanation might help...

 

for this issue there's a table of `games`:

`id`, `game_title`

 

a table of game `ratings`:

`game_id`, `user_id`, `rating`

 

and a table of `users`:

`id`, `username`, `banned`

 

what i want to do is get a list of all games, and an average of their ratings, but not include the ratings if the `banned` value in the `users` table is set to 'yes'. can anyone point me in the right direction as far as how to join up in this method? thanks so much.

 

psuedo:

selecting from `games

joining `ratings` based on the `game` `id`

joining `users` based on the `ratings` `user_id`???

[fenway]

Then you need to check users.banned in the ON clause.