In the last episode (Dec 20), Johnny Withers said:
> On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair <msubair@stripped> 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
dnelson@stripped
