My bad. As it turns out, there are duplicates in the IN() listing. It must be that mysql 'uniques' the list before operating on it.
> -----Original Message----- > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 24, 2005 5:14 PM > To: mysql@lists.mysql.com > Subject: Is there something broken with IN()? WAS: How can I > find the records that are NOT IN the list > > Now I'm really confused. I decided to just brute force this > while I waited > for a real answer. I threw in a couple test numbers I knew would fail > 5555555 and 66666. How come none of the values aside from the > two I threw in > for good measure are failing?! Is there something broken with > "IN()" on > v4.0.18, for pc-linux-gnu on i686 > > <?php > $foo = array (5555555, 11704, 10144, 11842, 11299, 11192, > 11563, 11378, > 10343, 66666, 10752, 10626, 11916, 11456, 11813, 11337, > 10219, 11356, 11800, > 10243, 10251, 11357, 11353, 11267, 12110, 200044, 11875, 11060, 10233, > 10865, 10264, 10857, 10659, 10266, 10987, 11317, 11868, > 10858, 11087, 11088, > 10260, 11321, 11350, 10247, 10258, 10248, 11232, 10588, > 11086, 11828, 10055, > 11347, 10278, 11349, 10261, 11499, 11351, 11316, 12284, > 12240, 12265, 11340, > 10708, 11041, 11853, 12255, 11507, 11788, 10067, 10888, > 11875, 11333, 10867, > 10938, 11030, 10654, 10538, 10918, 11068, 11237, 11060, > 10127, 10495, 10035, > 10294, 10173, 10098, 10282, 10383, 10049, 10076, 10277, > 10106, 10541, 10176, > 10131, 10252, 11051, 11152, 11932, 11318, 10886, 10605, > 10029, 11857, 10549, > 10329, 11510, 10539); > echo "foo = ".count($foo)."<BR>"; > > $i = 1; > foreach ($foo as $k => $v) > { > echo $i++." "; > $sth = SQL_QUERY('SELECT id FROM mytable WHERE id = > '.$v.' LIMIT 1', > true); > if ($sth) > { > if (SQL_NUM_ROWS($sth) < 1) echo "<B>CANNOT FIND: > ".$v."</B><BR>"; > } > } > ?> > > foo = 109 > 1 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 5555555 > LIMIT 1 > CANNOT FIND VULN: 5555555 > 2 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11704 > LIMIT 1 > 3 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10144 > LIMIT 1 > 4 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11842 > LIMIT 1 > 5 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11299 > LIMIT 1 > 6 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11192 > LIMIT 1 > 7 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11563 > LIMIT 1 > 8 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11378 > LIMIT 1 > 9 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10343 > LIMIT 1 > 10 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 66666 > LIMIT 1 > CANNOT FIND VULN: 66666 > 11 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10752 > LIMIT 1 > 12 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10626 > LIMIT 1 > 13 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11916 > LIMIT 1 > 14 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11456 > LIMIT 1 > 15 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11813 > LIMIT 1 > 16 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11337 > LIMIT 1 > 17 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10219 > LIMIT 1 > 18 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11356 > LIMIT 1 > 19 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11800 > LIMIT 1 > 20 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10243 > LIMIT 1 > 21 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10251 > LIMIT 1 > 22 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11357 > LIMIT 1 > 23 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11353 > LIMIT 1 > 24 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11267 > LIMIT 1 > 25 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 12110 > LIMIT 1 > 26 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 200044 > LIMIT 1 > 27 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11875 > LIMIT 1 > 28 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11060 > LIMIT 1 > 29 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10233 > LIMIT 1 > 30 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10865 > LIMIT 1 > 31 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10264 > LIMIT 1 > 32 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10857 > LIMIT 1 > 33 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10659 > LIMIT 1 > 34 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10266 > LIMIT 1 > 35 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10987 > LIMIT 1 > 36 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11317 > LIMIT 1 > 37 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11868 > LIMIT 1 > 38 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10858 > LIMIT 1 > 39 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11087 > LIMIT 1 > 40 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11088 > LIMIT 1 > 41 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10260 > LIMIT 1 > 42 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11321 > LIMIT 1 > 43 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11350 > LIMIT 1 > 44 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10247 > LIMIT 1 > 45 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10258 > LIMIT 1 > 46 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10248 > LIMIT 1 > 47 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11232 > LIMIT 1 > 48 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10588 > LIMIT 1 > 49 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11086 > LIMIT 1 > 50 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11828 > LIMIT 1 > 51 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10055 > LIMIT 1 > 52 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11347 > LIMIT 1 > 53 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10278 > LIMIT 1 > 54 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11349 > LIMIT 1 > 55 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10261 > LIMIT 1 > 56 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11499 > LIMIT 1 > 57 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11351 > LIMIT 1 > 58 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11316 > LIMIT 1 > 59 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 12284 > LIMIT 1 > 60 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 12240 > LIMIT 1 > 61 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 12265 > LIMIT 1 > 62 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11340 > LIMIT 1 > 63 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10708 > LIMIT 1 > 64 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11041 > LIMIT 1 > 65 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11853 > LIMIT 1 > 66 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 12255 > LIMIT 1 > 67 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11507 > LIMIT 1 > 68 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11788 > LIMIT 1 > 69 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10067 > LIMIT 1 > 70 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10888 > LIMIT 1 > 71 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11875 > LIMIT 1 > 72 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11333 > LIMIT 1 > 73 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10867 > LIMIT 1 > 74 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10938 > LIMIT 1 > 75 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11030 > LIMIT 1 > 76 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10654 > LIMIT 1 > 77 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10538 > LIMIT 1 > 78 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10918 > LIMIT 1 > 79 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11068 > LIMIT 1 > 80 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11237 > LIMIT 1 > 81 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11060 > LIMIT 1 > 82 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10127 > LIMIT 1 > 83 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10495 > LIMIT 1 > 84 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10035 > LIMIT 1 > 85 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10294 > LIMIT 1 > 86 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10173 > LIMIT 1 > 87 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10098 > LIMIT 1 > 88 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10282 > LIMIT 1 > 89 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10383 > LIMIT 1 > 90 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10049 > LIMIT 1 > 91 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10076 > LIMIT 1 > 92 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10277 > LIMIT 1 > 93 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10106 > LIMIT 1 > 94 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10541 > LIMIT 1 > 95 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10176 > LIMIT 1 > 96 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10131 > LIMIT 1 > 97 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10252 > LIMIT 1 > 98 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11051 > LIMIT 1 > 99 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11152 > LIMIT 1 > 100 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11932 > LIMIT 1 > 101 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11318 > LIMIT 1 > 102 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10886 > LIMIT 1 > 103 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10605 > LIMIT 1 > 104 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10029 > LIMIT 1 > 105 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11857 > LIMIT 1 > 106 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10549 > LIMIT 1 > 107 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10329 > LIMIT 1 > 108 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 11510 > LIMIT 1 > 109 SQL: SELECT scan_id FROM V2_Data.pluginlist_view WHERE > scan_id = 10539 > LIMIT 1 > total = 110 > > > > -----Original Message----- > > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > > Sent: Thursday, February 24, 2005 4:20 PM > > To: mysql@lists.mysql.com > > Subject: How can I find the records that are NOT IN the list > > > > I have an "IN()" list of 107 IDs (PK) out of about 6000 possible. > > > > I do this query and I get 105 rows back. > > > > I want to know which two [107 - 105 = 2] of the IDs in the > > "IN()" list are > > absent? > > > > # 105 rows > > SELECT count(*) > > FROM mytable > > WHERE id IN (11704, 10144, 11842, 11299, 11192, 11563, 11378, > > 10343, 10752, > > 10626, 11916, 11456, 11813, 11337, 10219, 11356, 11800, > > 10243, 10251, 11357, > > 11353, 11267, 12110, 200044, 11875, 11060, 10233, 10865, > 10264, 10857, > > 10659, 10266, 10987, 11317, 11868, 10858, 11087, 11088, > > 10260, 11321, 11350, > > 10247, 10258, 10248, 11232, 10588, 11086, 11828, 10055, > > 11347, 10278, 11349, > > 10261, 11499, 11351, 11316, 12284, 12240, 12265, 11340, > > 10708, 11041, 11853, > > 12255, 11507, 11788, 10067, 10888, 11875, 11333, 10867, > > 10938, 11030, 10654, > > 10538, 10918, 11068, 11237, 11060, 10127, 10495, 10035, > > 10294, 10173, 10098, > > 10282, 10383, 10049, 10076, 10277, 10106, 10541, 10176, > > 10131, 10252, 11051, > > 11152, 11932, 11318, 10886, 10605, 10029, 11857, 10549, > 10329, 11510, > > 10539); > > > > > > > > -- > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
