I need some help getting MySql to output some results using a subquery

I'm storing a list of numbers inside a table as a varchar(255) and want to use this list in another query's "IN() clause.

Here's what I mean:

Table Data:

CREATE TABLE IF NOT EXISTS `session_data` (
  `visible_portf_ids` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `session_data` (`visible_portf_ids`) VALUES
('45,44,658,659,661,45,44,658,659,661')

I want to run a query like this to return a list of portfolio's "QUERY #1":

SELECT portfolio_hierarchy_id, account_id, name, leaf_node_portf_id 
FROM portfolio_hierarchy
WHERE account_id = 1
AND leaf_node_portf_id IN 
            (
                 (SELECT visible_portf_ids
                 FROM session_data
                 WHERE username = 'ronedog')
            )
ORDER BY name ASC

The result of the query above returns only 1 row, when there are a total of 3 that should have been returned.

If I run the subquery alone like this:

(SELECT visible_portf_ids
FROM session_data
WHERE username = 'ronedog')

it will return a list like this:

45,44,658,659,661,45,44,658,659,661

But, when I run Query #1 above, only one row of data, which is associated with the "visible_portf_ids" of "45" is returned.

If I replace the subquery with hard coded values like开发者_开发技巧 this:

SELECT portfolio_hierarchy_id, account_id, name, leaf_node_portf_id 
FROM portfolio_hierarchy
WHERE account_id = 1
AND leaf_node_portf_id IN (45,44,658,659,661,45,44,658,659,661)
ORDER BY name ASC

then I get all 3 rows I'm expecting.

I'm guessing that MySql is returning the list as a string because its stored as a varchar() and so it stops processing after the first "visible_portf_ids" is found, which is "45", but I'm not really sure.

Anyone got any ideas how I can fix this?

Thanks in advance.

You should think about restructuring your tables storing each value in a new row, instead of concatenating them.

Until then, you can use the FIND_IN_SET() function:

AND FIND_IN_SET(leaf_node_portf_id,
                 (SELECT visible_portf_ids
                 FROM session_data
                 WHERE username = 'ronedog'
                 LIMIT 1)
            ) > 0

Unfortunately MySQL does not have a function to split a delimited string. Your IN argument is a single string with the result of your subquery. The reason it works when you hard-code it is that MySQL is parsing the values.

I suggest that you redesign your data base to store the visible ports list as separate rows in a separate table. Then you can retrieve them and use them in subqueries like you tried.