SicaYoumi SicaYoumi - 1 month ago 7
SQL Question

Fast SQL checking for mutliple records

May I ask that I have to perform checking on the userId column but the input may be more than 1.

THE CONDITION

I have to check whether the userId(input(s)) is one of the record inside the table or not.

IF all of them inside the table

SQL return 1

IF one of them is NOT

SQL return 0

I'm a newbie and what I can do is only

SELECT count(1) from users_roles where userid = ?


and using FOR loop in the java coding to insert the params and rerun this SQL.

Is there any other method or way i can do this with faster way? I'm thinking that if the input become more and more, it may take many resource to process it.

I'm a newbie in SQL. Sry for answer silly question.

Answer

I think you want something like this:

select case when count(*) = 3 then 1 else 0 end) as all_there
from users_roles
where userid in (?, ?, ?);

The 3 is equal to the number of parameters in the in list.