List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 15 2003 7:00pm
Subject:Re: Question about IN operator
View as plain text  
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!

Thread
Question about IN operatorDon! Briggs15 Feb
  • Re: Question about IN operatorPaul DuBois15 Feb