Description:
i am doing a fairly straight-forward 2-join select which works as expected on DB2, but does strange things on mysql 4.0.18.
i have 3 tables: players, teams, and team_members. TABLE players has a pool of all available players. TABLE teams has all teams. a group of teams comprises one league. TABLE team_members reflects which players are on which team. so if player joe was on team jets, there would be an entry in team_members referencing the jets and joe. there is a restriction that no player appear on more than one team in any given league. that is, only the jets can have joe in their league. but in some another league, a different team can have joe.
my query selects all the players NOT on any team in a given league with no nested queries because i am on mysql 4.0.18.
here is the database dump, reduced to its absolute bare minimum (it's very small):
CREATE TABLE team_members (
team_member_id int(10) unsigned NOT NULL auto_increment,
team_id int(10) unsigned NOT NULL default '0',
player_id mediumint(8) NOT NULL default '0',
PRIMARY KEY (team_member_id)
) TYPE=MyISAM;
INSERT INTO team_members VALUES (730,515,4590);
INSERT INTO team_members VALUES (729,514,302154);
INSERT INTO team_members VALUES (728,513,396138);
INSERT INTO team_members VALUES (727,512,396139);
INSERT INTO team_members VALUES (726,511,4590);
CREATE TABLE players (
player_id mediumint(8) NOT NULL default '0',
first_name varchar(50) default NULL,
PRIMARY KEY (player_id)
) TYPE=MyISAM;
INSERT INTO players VALUES (133460,'Ryan');
INSERT INTO players VALUES (396138,'Walter');
INSERT INTO players VALUES (396139,'Dave');
INSERT INTO players VALUES (302154,'Pat');
INSERT INTO players VALUES (146369,'Joe');
INSERT INTO players VALUES (133461,'Peppi');
INSERT INTO players VALUES (133462,'Amos');
INSERT INTO players VALUES (4590,'Jeff');
INSERT INTO players VALUES (192939,'Chris');
INSERT INTO players VALUES (397803,'Adam');
INSERT INTO players VALUES (484894,'Kenny');
INSERT INTO players VALUES (235186,'Paul');
CREATE TABLE teams (
team_id int(10) unsigned NOT NULL auto_increment,
team_name varchar(100) NOT NULL default '',
league_id varchar(45) NOT NULL default '0',
PRIMARY KEY (team_id)
) TYPE=MyISAM;
INSERT INTO teams VALUES (511,'Jets','league1');
INSERT INTO teams VALUES (512,'Browns','league1');
INSERT INTO teams VALUES (513,'Falcons','league1');
INSERT INTO teams VALUES (514,'Patriots','league2');
INSERT INTO teams VALUES (515,'Bengals','league3');
----------------------------------------
and here is the query:
SELECT
p.*
FROM players p
LEFT JOIN
team_members m JOIN teams t
ON t.league_id = 'league1'
AND t.team_id = m.team_id
ON p.player_id = m.player_id
WHERE m.team_id IS NULL;
on db2, it yields the set of players in TABLE players but not on any team in league1, { ryan, pat, joe, peppi, amos, chris, adam, kenny, paul }. on mysql, it yields a larger mess that i cannot make sense of.
How to repeat:
just load the database dump i have above, and try the select statement. either mysql is handling it wrong, or i am doing something very wrong in which case i'd very much appreciate an explanation of why this statement works on db2 (or any other commercial db) and not mysql.
thanks a lot.
Description: i am doing a fairly straight-forward 2-join select which works as expected on DB2, but does strange things on mysql 4.0.18. i have 3 tables: players, teams, and team_members. TABLE players has a pool of all available players. TABLE teams has all teams. a group of teams comprises one league. TABLE team_members reflects which players are on which team. so if player joe was on team jets, there would be an entry in team_members referencing the jets and joe. there is a restriction that no player appear on more than one team in any given league. that is, only the jets can have joe in their league. but in some another league, a different team can have joe. my query selects all the players NOT on any team in a given league with no nested queries because i am on mysql 4.0.18. here is the database dump, reduced to its absolute bare minimum (it's very small): CREATE TABLE team_members ( team_member_id int(10) unsigned NOT NULL auto_increment, team_id int(10) unsigned NOT NULL default '0', player_id mediumint(8) NOT NULL default '0', PRIMARY KEY (team_member_id) ) TYPE=MyISAM; INSERT INTO team_members VALUES (730,515,4590); INSERT INTO team_members VALUES (729,514,302154); INSERT INTO team_members VALUES (728,513,396138); INSERT INTO team_members VALUES (727,512,396139); INSERT INTO team_members VALUES (726,511,4590); CREATE TABLE players ( player_id mediumint(8) NOT NULL default '0', first_name varchar(50) default NULL, PRIMARY KEY (player_id) ) TYPE=MyISAM; INSERT INTO players VALUES (133460,'Ryan'); INSERT INTO players VALUES (396138,'Walter'); INSERT INTO players VALUES (396139,'Dave'); INSERT INTO players VALUES (302154,'Pat'); INSERT INTO players VALUES (146369,'Joe'); INSERT INTO players VALUES (133461,'Peppi'); INSERT INTO players VALUES (133462,'Amos'); INSERT INTO players VALUES (4590,'Jeff'); INSERT INTO players VALUES (192939,'Chris'); INSERT INTO players VALUES (397803,'Adam'); INSERT INTO players VALUES (484894,'Kenny'); INSERT INTO players VALUES (235186,'Paul'); CREATE TABLE teams ( team_id int(10) unsigned NOT NULL auto_increment, team_name varchar(100) NOT NULL default '', league_id varchar(45) NOT NULL default '0', PRIMARY KEY (team_id) ) TYPE=MyISAM; INSERT INTO teams VALUES (511,'Jets','league1'); INSERT INTO teams VALUES (512,'Browns','league1'); INSERT INTO teams VALUES (513,'Falcons','league1'); INSERT INTO teams VALUES (514,'Patriots','league2'); INSERT INTO teams VALUES (515,'Bengals','league3'); ---------------------------------------- and here is the query: SELECT p.* FROM players p LEFT JOIN team_members m JOIN teams t ON t.league_id = 'league1' AND t.team_id = m.team_id ON p.player_id = m.player_id WHERE m.team_id IS NULL; on db2, it yields the set of players in TABLE players but not on any team in league1, { ryan, pat, joe, peppi, amos, chris, adam, kenny, paul }. on mysql, it yields a larger mess that i cannot make sense of. How to repeat: just load the database dump i have above, and try the select statement. either mysql is handling it wrong, or i am doing something very wrong in which case i'd very much appreciate an explanation of why this statement works on db2 (or any other commercial db) and not mysql. thanks a lot.