Using a string of a SELECT query to put in on a IN () condition

Question

How can I use an string input as 1,2,3,4 taken from a SELECT to put in on an IN condition?

I mean, having this output from this:

SELECT id_list
FROM ids
where categories = 4;

id_list is a string like 1,2,3,4.

Now I want to use that string to put in inside a IN ():

SELECT category_name
FROM categories
WHERE categories IN (
    SELECT id_list
    FROM ids
    where categories = 4
)

But this only outputs the result of the first value of the string, in this case, 1.

I know it's not the best implementation, but I'm not the one who made the DB design and I need to make the query this way. This is just a simplified example.

Can someone tell me how con I archieve my goal? Thanks in advance!

Can you provide some information on the structure of category_name and on what results you expected to get? — Brian Dewhirst, Nov 21 '17 at 13:13
Smells like bad design storing relations as comma separated values ? — M Khalid Junaid, Nov 21 '17 at 13:14
I know it's not the best implementation, but I'm not the one who made the DB design and I need to make the query this way. This is just a simplified example. — Avión, Nov 21 '17 at 13:15

score 2 · Accepted Answer · answered Nov 21 '17 at 13:20

2

You can use FIND_IN_SET

Query

SELECT `category_name`
FROM `categories`
WHERE FIND_IN_SET(`id`, (SELECT `id_list` FROM `ids` WHERE `categories` = 4)) > 0;

Find a fiddle demo here

answered Nov 21 '17 at 13:20

Ullas

11,450
4
33
50

Thanks for the magic :) – Avión Nov 21 '17 at 13:29

Using a string of a SELECT query to put in on a IN () condition

1 Answers1