How to filtering MySQL table using another query result

Question

I have two tables:

UserTable

id  nama
--  -------------------
1   Administrator
11  Agung
21  Sagita
50  John
100 Purnama

Filtertable

id  idlist
--  ------------------
1   1,21,100
2   21,50

So I want to query name table with id from the second table

SELECT id, nama FROM UserTable
WHERE id IN (SELECT idlist FROM FilterTable WHERE id =1) 

But In MySQL the result only show

    id  nama           
------  ---------------
     1  Administrator  

While it should show at least 3 rows from UserTable

I think this should be easy but I've tried search google and stackoverflow without solution, kindly show me how to get what I need

This is a simplification, further implementation is for saving search history (the table's ids) from visitors

Answer 1

You won't get the results you expect if you use a string (the result of your (SELECT idlist FROM FilterTable WHERE id =1) subquery) as an input to an IN expression; MySQL will attempt to convert it to an integer to match the value (id) being tested, resulting in 1,21,100 being converted to 1 and matching only the Administrator value. Instead, you should use FIND_IN_SET to compare:

SELECT u.id, u.nama
FROM UserTable u
JOIN FilterTable f ON FIND_IN_SET(u.id, f.idlist)
WHERE f.id = 1

Or (using a subquery)

SELECT id, nama
FROM UserTable u
WHERE FIND_IN_SET(id, (SELECT idlist FROM FilterTable WHERE id =1))

Output:

id      nama
1       Administrator
21      Sagita
100     Purnama

Demo on dbfiddle

Your life would be much easier if your schema was properly normalised. Please read: Is storing a delimited list in a database column really that bad?