You are misunderstanding the 'IN' operator Try and think of it this way
SELECT value from table where field in (1,2,3) is the same as SELECT value from table where field = 1 or field = 2 or field = 3 So in you case SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); is SELECT Count(ID) FROM ZipCounty WHERE '528' = (VendorLink); In your case I would try using the 'LIKE' operator SELECT Count(ID) FROM ZipCounty WHERE (VendorLink) like '%528%'; -Michael "Don't shed tears for those already in the ground, until after you have brought vengeance to those who put them there. There will be time enough then." -Kahlan On Sat, 6 Sep 2003, Roger Davis wrote: > I am having a bit of trouble with the "IN" operator. I am thinking that it > just may be my misunderstanding. > > Situation. > I have a table (ZipCounty) that I want to pull vendors from based on > zipcode. So I have a Vendor like field declared as TEXT. To keep the table > up to date, I issuse updates in the manor of... > > UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'CA' AND 528 NOT IN > (VendorLink); > > Where 528 would be the ID of that particular vendor. These updates work > fine, but later if I need to do another update, say to remove 528 from the > VendorLink field, I can find all of the the records where 528 is the only > number in the field, or if it is the first number in the field, but not if > it is the last number in the field. > > Hope someone can help me out. > > Roger > > Here are a few selects > > mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529,528'; > +-----------+ > | Count(ID) | > +-----------+ > | 1384 | > +-----------+ > 1 row in set (0.25 sec) > > mysql> SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); > +-----------+ > | Count(ID) | > +-----------+ > | 0 | > +-----------+ > 1 row in set (0.25 sec) > > mysql> SELECT VendorLink, Count(ID) FROM ZipCounty WHERE VendorLink IS NOT > NULL GROUP BY VendorLink; > +------------+-----------+ > | VendorLink | Count(ID) | > +------------+-----------+ > | 529 | 3844 | > | 529,528 | 1384 | > +------------+-----------+ > 2 rows in set (0.61 sec) > > Why do I get a count of 0 for the Second Query? > > > Here is a list of all the queries and output. (redhat 9.0 version 2.23.54) > > This also happens on the latest Windows version (mysql Ver 12.21 Distrib > 4.0.14, for Win95/Win98 (i32)) > > > mysql> \s > -------------- > mysql Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386) > > Connection id: 59756 > Current database: TPZ > Current user: [EMAIL PROTECTED] > Current pager: stdout > Using outfile: '' > Server version: 3.23.54 > Protocol version: 10 > Connection: Localhost via UNIX socket > Client characterset: latin1 > Server characterset: latin1 > UNIX socket: /var/lib/mysql/mysql.sock > Uptime: 12 days 19 hours 38 min 36 sec > > Threads: 2 Questions: 2354432 Slow queries: 43 Opens: 143 Flush tables: > 1 Open tables: 64 Queries per second avg: 2.126 > -------------- > > mysql> SHOW CREATE TABLE ZipCounty; > +-----------+--------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ------------------------+ > | Table | Create Table > | > +-----------+--------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ------------------------+ > | ZipCounty | CREATE TABLE `ZipCounty` ( > `ID` int(11) NOT NULL auto_increment, > `City` varchar(50) default NULL, > `State` char(2) default NULL, > `Zip` varchar(10) default NULL, > `AreaCode` char(3) default NULL, > `County` varchar(50) default NULL, > `VendorLink` text, > `OtherLink` text, > PRIMARY KEY (`ID`), > KEY `Zip` (`Zip`), > KEY `County` (`County`), > KEY `State` (`State`) > ) TYPE=MyISAM | > +-----------+--------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ---------------------------------------------------------------------------- > ------------------------+ > 1 row in set (0.00 sec) > > mysql> UPDATE ZipCounty SET VendorLink = NULL; > Query OK, 11193 rows affected (0.99 sec) > Rows matched: 69802 Changed: 11193 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'529',CONCAT(VendorLink,',529')) WHERE State = 'AL' AND 529 NOT IN > (VendorLink); > Query OK, 1312 rows affected (0.08 sec) > Rows matched: 1312 Changed: 1312 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'529',CONCAT(VendorLink,',529')) WHERE State = 'AR' AND 529 NOT IN > (VendorLink); > Query OK, 1313 rows affected (0.07 sec) > Rows matched: 1313 Changed: 1313 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'529',CONCAT(VendorLink,',529')) WHERE County = 'Kent' AND State = 'DE' > AND 529 NOT IN (VendorLink); > Query OK, 30 rows affected (0.00 sec) > Rows matched: 30 Changed: 30 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'529',CONCAT(VendorLink,',529')) WHERE County = 'New Castle' AND State > = 'DE' AND 529 NOT IN (VendorLink); > Query OK, 70 rows affected (0.00 sec) > Rows matched: 70 Changed: 70 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'529',CONCAT(VendorLink,',529')) WHERE State = 'FL' AND 529 NOT IN > (VendorLink); > Query OK, 2503 rows affected (0.14 sec) > Rows matched: 2503 Changed: 2503 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Autauga' AND State = > 'AL' AND 528 NOT IN (VendorLink); > Query OK, 15 rows affected (0.00 sec) > Rows matched: 15 Changed: 15 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Baldwin' AND State = > 'AL' AND 528 NOT IN (VendorLink); > Query OK, 40 rows affected (0.00 sec) > Rows matched: 40 Changed: 40 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Barbour' AND State = > 'AL' AND 528 NOT IN (VendorLink); > Query OK, 7 rows affected (0.01 sec) > Rows matched: 7 Changed: 7 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Bibb' AND State = 'AL' > AND 528 NOT IN (VendorLink); > Query OK, 9 rows affected (0.00 sec) > Rows matched: 9 Changed: 9 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'AR' AND 528 NOT IN > (VendorLink); > Query OK, 1313 rows affected (0.09 sec) > Rows matched: 1313 Changed: 1313 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'CA' AND 528 NOT IN > (VendorLink); > Query OK, 3878 rows affected (0.20 sec) > Rows matched: 3878 Changed: 3878 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE State = 'IN' AND 528 NOT IN > (VendorLink); > Query OK, 2062 rows affected (0.09 sec) > Rows matched: 2062 Changed: 2062 Warnings: 0 > > mysql> UPDATE ZipCounty SET VendorLink = IF(VendorLink IS > NULL,'528',CONCAT(VendorLink,',528')) WHERE County = 'Brown' AND State = > 'SD' AND 528 NOT IN (VendorLink); > Query OK, 25 rows affected (0.00 sec) > Rows matched: 25 Changed: 25 Warnings: 0 > > mysql> SELECT Count(ID) FROM ZipCounty WHERE 529 IN (VendorLink); > +-----------+ > | Count(ID) | > +-----------+ > | 5228 | > +-----------+ > 1 row in set (0.28 sec) > > mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529'; > +-----------+ > | Count(ID) | > +-----------+ > | 3844 | > +-----------+ > 1 row in set (0.26 sec) > > mysql> SELECT Count(ID) FROM ZipCounty WHERE 528 IN (VendorLink); > +-----------+ > | Count(ID) | > +-----------+ > | 5965 | > +-----------+ > 1 row in set (0.28 sec) > > mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '528'; > +-----------+ > | Count(ID) | > +-----------+ > | 5965 | > +-----------+ > 1 row in set (0.26 sec) > > mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529,528'; > +-----------+ > | Count(ID) | > +-----------+ > | 1384 | > +-----------+ > 1 row in set (0.27 sec) > > mysql> SELECT COUNT(ID) FROM ZipCounty WHERE VendorLink IS NOT NULL; > +-----------+ > | COUNT(ID) | > +-----------+ > | 11193 | > +-----------+ > 1 row in set (0.28 sec) > > mysql> SELECT VendorLink, Count(ID) FROM ZipCounty WHERE VendorLink IS NOT > NULL GROUP BY VendorLink; > +------------+-----------+ > | VendorLink | Count(ID) | > +------------+-----------+ > | 528 | 5965 | > | 529 | 3844 | > | 529,528 | 1384 | > +------------+-----------+ > 3 rows in set (1.04 sec) > > > mysql> SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); > +-----------+ > | Count(ID) | > +-----------+ > | 5965 | > +-----------+ > 1 row in set (0.30 sec) > > mysql> UPDATE ZipCounty SET VendorLink = NULL WHERE VendorLink = '528'; > Query OK, 5965 rows affected (0.72 sec) > Rows matched: 5965 Changed: 5965 Warnings: 0 > > mysql> SELECT Count(ID) FROM ZipCounty WHERE VendorLink = '529,528'; > +-----------+ > | Count(ID) | > +-----------+ > | 1384 | > +-----------+ > 1 row in set (0.25 sec) > > mysql> SELECT Count(ID) FROM ZipCounty WHERE '528' IN (VendorLink); > +-----------+ > | Count(ID) | > +-----------+ > | 0 | > +-----------+ > 1 row in set (0.25 sec) > > mysql> SELECT VendorLink, Count(ID) FROM ZipCounty WHERE VendorLink IS NOT > NULL GROUP BY VendorLink; > +------------+-----------+ > | VendorLink | Count(ID) | > +------------+-----------+ > | 529 | 3844 | > | 529,528 | 1384 | > +------------+-----------+ > 2 rows in set (0.61 sec) > --- > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.512 / Virus Database: 309 - Release Date: 8/19/2003 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
