Re: Question about IN operator

[Paul DuBois <suppressed>]

At 12:41 -0600 2/15/03, Don! Briggs wrote:
> I have a field (cdfeature) that contains a list of comma seperated values.
> Example follows:
>
> select item_id, cdfeature from cars where (101 in (cdfeature));
> +---------+---------------------+
> | item_id | cdfeature           |
> +---------+---------------------+
> |     320 | 101,104,106         |
> |     321 | 101,103,105,112     |
> |     323 | 101,103,105,112     |
> |     316 | 101,102,104,108,112 |
> |     345 | 101,102,104,108,112 |
> +---------+---------------------+
>
> When I select only one value (ie, 101) the statement works. I need to be
> able to select based upon multinple IN statements. When I try this, the
> query returns an empty set. Example follows:
>
> select item_id, cdfeature from cars where (101 in (cdfeature)) and (112 in
> (cdfeature));
> Empty set (0.00 sec)
>
> The above query should have, I think, returned records that had BOTH a 101
> an a 112 in the cdfeature filed. Specifically, it should have returned
> records 321, 323, 316, 345. Somebody please help me out here!!!

That's not how IN() works.  It takes an argument list consisting of
a set of values, separated by commas.  You're providing a single
value that contains commas internally.  That's not the same thing
at all.

If there are 64 or fewer cdfeature values, you might consider representing
that column as a SET column.

> 	Don!


---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <suppressed>
To unsubscribe, e-mail <suppressed>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php