Reputation: 229
I am trying to get a random id value from my table. The condition is that the selected value must not exist for the same id and different language (language is lang
field)
I do this:
SELECT id_v as p,cont FROM value_phrase WHERE lang=1 AND
NULL IN (SELECT cont FROM value_phrase WHERE id_v=p AND lang=2)
ORDER BY RAND() LIMIT 0,1
... but I do not get any results (It must be an error in my query, because there are many rows satisfying the desired condition.) How should I fix it?
-- SOLVED --
the solution is to use NOT EXISTS instead of NULL IN, and use table aliases to refer the first id_v into the sub query. This way (thanks to Mark Byers):
SELECT first.id_v, first.cont
FROM value_phrase AS
FIRST WHERE lang =1
AND NOT
EXISTS (
SELECT cont
FROM value_phrase
WHERE id_v = first.id_v
AND lang =2
)
ORDER BY RAND( )
LIMIT 0 , 1
Upvotes: 0
Views: 83
Reputation:
Your condition NULL IN (...)
will never return anything, because a comparison with NULL
always returns NULL
and that is treated as "false" in a query condition.
Upvotes: 1
Reputation: 839254
If you want to check if a value does not exist, use NOT EXISTS
.
AND NOT EXISTS (SELECT ... )
Upvotes: 2