List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 20 2010 6:17pm
Subject:Re: Problem with WHERE .. IN
View as plain text  
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
Thread
Problem with WHERE .. INmuhammad subair20 Dec
  • Re: Problem with WHERE .. INJohnny Withers20 Dec
    • Re: Problem with WHERE .. INDan Nelson20 Dec