CODEWITHSUNDEEP
sqlsubquery
Kenedy
Kenedy

Reputation: 229

SELECT refering value to sub query

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

Answers (2)

user330315
user330315

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

Mark Byers
Mark Byers

Reputation: 839254

If you want to check if a value does not exist, use NOT EXISTS.

AND NOT EXISTS (SELECT ... )

Upvotes: 2

Related Questions