MySQL Forums
Forum List  »  Newbie

problem selecting from one table where not in another
Posted by: Israel Iglesias
Date: January 24, 2012 05:37AM

Hello everybody, I have a query to select players from one table where the player is not in another two tables, but for some reason sometimes it returns a player that actually is in one of the 2 tables. Here is the query:

SELECT
jugadores.*
FROM
jugadores

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

WHERE
jugadores.nivel='3'

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

ORDER BY RAND()
LIMIT 1

And for this particular case this would be the double check query I use later on to see if the player is a duplicate:

SELECT uj.id_liga, uj.id_jugador, COUNT(*) AS total FROM usuarios_jugadores AS uj
LEFT JOIN mercado_fichajes AS mf
ON
mf.id_liga='92c46a' AND
mf.id_jugador_real='3a8698'
WHERE
uj.id_liga='92c46a' AND
uj.id_jugador='3a8698'
TOTAL MATCHES FOR 3a8698, LIGA 92c46a: 1

The TOTAL MATCHES is just the script catching the duplicate.

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

Any ideas why this could be happening?

Thanks!

Options: ReplyQuote


Subject
Written By
Posted
problem selecting from one table where not in another
January 24, 2012 05:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.