Re: problem selecting from one table where not in another

[Israel Iglesias]

Sorry about that, is my first post...

The tables involved:

TABLE jugadores: This table holds the players that other teams can have
+--------------+------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------------------------------------+------+-----+---------+-------+
| id | varchar(6) | NO | PRI | | |
| nivel | tinyint(3) unsigned | NO | | 1 | |
+--------------+------------------------------------------------+------+-----+---------+-------+

CREATE TABLE jugadores (
  id varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  nivel tinyint(3) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

TABLE usuarios_jugadores: this table holds the players that the teams have
+--------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+----------------------+------+-----+---------+-------+
| id | varchar(6) | NO | PRI | | |
| id_liga | varchar(6) | NO | MUL | | |
| id_jugador | varchar(6) | NO | MUL | | |
+--------------------+----------------------+------+-----+---------+-------+

CREATE TABLE usuarios_jugadores (
  id varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  id_liga varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  id_jugador varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY id_liga (id_liga),
  KEY id_jugador (id_jugador)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

TABLE mercado_fichajes: this table is the players market where teams buy and sell players
+----------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------------------+------+-----+---------+-------+
| id | varchar(6) | NO | PRI | | |
| id_liga | varchar(6) | NO | MUL | | |
| id_jugador_real | varchar(6) | NO | MUL | | |
| oferta | int(10) unsigned | NO | | 0 | |
+----------------------+----------------------+------+-----+---------+-------+

CREATE TABLE mercado_fichajes (
  id varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  id_liga varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  id_jugador_real varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  oferta int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  KEY id_liga (id_liga),
  KEY id_jugador_real (id_jugador_real)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Some test data:

INSERT INTO jugadores VALUES ('aaaaaa',3),('bbbbbb',3),('cccccc',3);
INSERT INTO usuarios_jugadores VALUES ('ooooo1','ligaa','aaaaaa'),('ooooo2','ligaa','bbbbbb');
INSERT INTO mercado_fichajes VALUES ('mmmmm1','ligaa','aaaaaa',200000);

In this situation we have 3 players, 2 of which are in a team that plays on league 'ligaa', and one of which is being sold on the marketplace. The following query should always return 'cccccc' as que only possible option:

SELECT 
jugadores.* 
FROM 
jugadores

LEFT JOIN 
usuarios_jugadores AS uj
ON 
jugadores.id=uj.id_jugador
AND
uj.id_liga='ligaa'
LEFT JOIN 
mercado_fichajes AS mj
ON 
jugadores.id=mj.id_jugador_real
AND
mj.id_liga='ligaa'

WHERE 
jugadores.nivel='3' 

AND
uj.id_jugador IS NULL
AND
mj.id_jugador_real IS NULL

ORDER BY RAND() 
LIMIT 1

I have also tried with NOT IN and imploding the
array of ids but that doesn't seem to work
either.

+------------+
| VERSION() |
+------------+
| 5.1.56-log |
+------------+

Any ideas why this could be happening?

Thanks!