Mysql Query where field in (select column value) not works

Question

I am trying to get some values with the help of where clause and the value for that where clause is the values from a record in the same table.

For example,

select requestedusers from users where username = 'xyz'

When I run the above query it gives me result like abc,mno,tuv

I used that result in another query like select data from users where username in (abc,mno,tuv) and I am getting the finalized result.

But I need to obtain the finalized result in a single query like select data from users where username in (select requestedusers from users where username = 'xyz');.

When I try to run query like this it return me empty set.

The use of variables in your question is confusing. – ultrajohn Jun 01 '18 at 09:59 — ultrajohn, Jun 01 '18 at 09:59
Please add sample data and expected output. – HoneyBadger Jun 01 '18 at 10:04 — HoneyBadger, Jun 01 '18 at 10:04

score 2 · Accepted Answer · answered Jun 01 '18 at 10:24

Fix your data structure! Don't store comma-delimited lists in a single column. SQL has this really great data structure for lists. It is called a table, not a string.

You want a junction table; that is, a table with one row per user and per requested user.

That said, sometimes we are stuck with other people's really, really, really bad design decisions. In this cases, you can use find_in_set():

select data
from users u
where exists (select 1 
              from users u2
              where find_in_set(u.username, u2.requestedusers) > 0 and
                    u2.username = 'xyz'
             );

Performance will be bad. And there is little way to fix performance other than fixing the data model.

Mysql Query where field in (select column value) not works

1 Answers1