Problem with WHERE .. IN

--00163630f209ef8bc10497db2d09
Content-Type: text/plain; charset=ISO-8859-1

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

Thanks & Regards
--
Muhammad Subair
+62 8176583311

--00163630f209ef8bc10497db2d09--

Re: Problem with WHERE .. IN

--00163628523cbc11af0497db4847
Content-Type: text/plain; charset=ISO-8859-1

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).

On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair 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
>
> Thanks & Regards
> --
> Muhammad Subair
> +62 8176583311
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--00163628523cbc11af0497db4847--

Re: Problem with WHERE .. IN

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org