In the last episode (Dec 20), Johnny Withers said: > On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair <msub...@gmail.com> wrote: > > I have table post (id INT and parent VARCHAR) > > > > +------+-------------+ > > | id | parent | > > +------+-------------+ > > | 1 | 0 | > > | 2 | 0 | > > | 3 | 1 | > > | 4 | 0 | > > | 5 | 1 | > > | 6 | 0 | > > | 7 | 1,5 | > > | 8 | 1,5 | > > | 9 | 1,5 | > > | 10 | 5,7,11 | > > | 11 | 1,5,7,10 | > > | 12 | 1,5,7,10,11 | > > +------+-------------+ > > > > SELECT id FROM post WHERE where id IN (SELECT parent FROM post WHERE id = > > 10); > > +------+ > > | id | > > +------+ > > | 5 | > > +------+ > > > > whereas the results I want is > > > > +------+ > > | id | > > +------+ > > | 5 | > > | 7 | > > | 11 | > > +------+ > > > > Please tell me, where is wrong > > The sub-select only returns a single row, so IN(...) is only looking at a > single value in the list .. it doesn't "expand" to into IN (5,7,11).
You might need to use the FIND_IN_SET function: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set so something like this should work (although the 2nd query won't be able to use any indexes): SELECT parent FROM post WHERE id = 10 into @parent; SELECT id from post where FIND_IN_SET(id, @parent) > 0; If you normalize your table so that you have one row per relation: +------+-------------+ | id | parent | +------+-------------+ | 10 | 5 | | 10 | 7 | | 10 | 11 | +------+-------------+ , then your original query would work the way you expected. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
