Doubt about SELECT command using IN

Question

I am mounting a select in Mysql as follows:

select lista, 
 (select if (count(login.LogID) > 0, 'S', 'N') from login where login.LogID = 407 and login.LogID in (11,157,66,158,407)) as Seguindo
from Comentarios

If I run this select in my database it returns 'S' in the field Seguindo, but if I replace mine in as follows:

select lista, 
 (select if (count(login.LogID) > 0, 'S', 'N') from login where login.LogID = 407 and login.LogID in (lista)) as Seguindo
from Comentarios

Where Lista is a table field comentarios with the content 11,157,66,158,407, Mysql returns as N in the field Seguindo.

My field lista is the type varchar. Would anyone know the reason for this behavior? And how would it be possible to get around it?

Answer 1

Let’s go in pieces

1º SQL

select lista, 
 (select if (count(login.LogID) > 0, 'S', 'N') from login where login.LogID = 407 and login.LogID in (11,157,66,158,407)) as Seguindo
from Comentarios

It works correctly because you validate your LogID within a list of integers (11,157,66,158,407).

2º SQL

select lista, 
 (select if (count(login.LogID) > 0, 'S', 'N') from login where login.LogID = 407 and login.LogID in (lista)) as Seguindo
from Comentarios

It doesn’t work because like you said the field lista is a varchar, just when you pass him on in in this way (lista) the field is being understood as a list value only, not multiple values.

Understand

Has 5 items in the list: (11,157,66,158,407) or ('11','157','66','158','407')
Has 1 item in the list: ('11,157,66,158,407')

Each value of a list is a row returned from a select

To get around it you can do it this way

select lista, 
 (select if (count(login.LogID) > 0, 'S', 'N') from login where login.LogID = 407 and  FIND_IN_SET(login.LogID, lista)) as Seguindo
from Comentarios

If you want to know more about the FIND_IN_SET

Answer 2

Friend, you said the content of the Comment table is 11,157,66,158,407, if you meant that when doing select in this table it returns only 1 row with this value, then the problem is this, you are not passing a list in your condition in select.

Now, if when doing select it returns you 5 lines and each line with a value, like this:

linha 1 -> 11;  
linha 2 -> 157;  
linha 3 -> 66;  
linha 4 -> 158;  
linha 5 -> 407;

You can mount your select so:

select lista, 
 (select if (count(login.LogID) > 0, 'S', 'N') from login where login.LogID = 407 and login.LogID in (select lista from comentarios)) as Seguindo
from Comentarios

See here a example from the above select.