If I've got the following two tables:
CREATE TABLE password_log (
time_stamp int(11) unsigned NOT NULL default '0',
remote_host char(15) NOT NULL default '',
remote_user char(50) NOT NULL default '',
status smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (remote_host,remote_user,status),
KEY time_stamp (time_stamp),
KEY remote_user (remote_user),
KEY status (status)
) TYPE=MyISAM;
CREATE TABLE exclude_log (
ip_block char(15) NOT NULL default '',
PRIMARY KEY (ip_block)
) TYPE=MyISAM;
#
# Dumping data for table 'exclude_log'
#
INSERT INTO exclude_log VALUES ('152.163.188');
INSERT INTO exclude_log VALUES ('152.163.189');
INSERT INTO exclude_log VALUES ('152.163.206');
INSERT INTO exclude_log VALUES ('152.163.207');
INSERT INTO exclude_log VALUES ('195.93.64');
INSERT INTO exclude_log VALUES ('195.93.65');
INSERT INTO exclude_log VALUES ('195.93.66');
INSERT INTO exclude_log VALUES ('195.93.72');
INSERT INTO exclude_log VALUES ('195.93.73');
INSERT INTO exclude_log VALUES ('195.93.74');
INSERT INTO exclude_log VALUES ('205.188.208');
INSERT INTO exclude_log VALUES ('205.188.209');
And I want a list of everything in the 'password_log' table that doesn't
match up with any entry in the 'exclude_log' table, something along the
lines of:
select remote_user, substring_index(remote_host,'.',3) As ip_subnet from
password_log, exclude_log where remote_user != '-' and status=200 and
substring_index(remote_host,'.',3) != exclude_log.ip_block group by
ip_subnet order by remote_user, ip_subnet\g
+---------------------------+-------------+
| remote_user | ip_subnet |
+---------------------------+-------------+
| adamvernau | 207.79.8 |
| amos | 24.53.232 |
| badmilk | 62.57.227 |
| [EMAIL PROTECTED] | 80.103.137 |
| beerbomb60 | 12.80.11 |
| BogusBob | 65.58.37 |
| brendenm123 | 172.190.203 |
| brize | 217.39.73 |
| bruneau | 195.242.80 |
| chicken | 24.101.127 |
| cracking | 213.122.143 |
| DanielNoble | 172.151.183 |
| DESIO | 204.213.78 |
| diamond | 4.60.97 |
| dlgeo | 68.42.127 |
| ewing | 195.29.35 |
| fnadeau | 64.228.196 |
| frogman | 67.234.8 |
| fujerome | 156.143.132 |
| geno6969 | 65.58.94 |
| gravy01 | 81.86.119 |
| iftkharmaan | 62.255.64 |
| jamesz | 204.186.14 |
| jaysap | 12.235.160 |
| karak | 80.63.120 |
| kevin | 152.163.188 |
| kevin | 152.163.189 |
| kevin | 152.163.201 |
| kjelljanssonx | 213.66.154 |
| ksm70512 | 172.195.152 |
| leinad | 210.120.128 |
| leinad | 66.68.138 |
| lemurs | 24.60.185 |
| leolebr | 81.48.138 |
| Malakon | 24.186.21 |
| martisr | 217.39.29 |
| matglew | 81.98.84 |
| mikeestela | 129.106.169 |
| Mirhos | 80.11.19 |
| newyork | 62.134.74 |
| ordinary3 | 12.37.234 |
| pcomdh | 212.185.249 |
| pp-hosereed | 24.61.65 |
| pp-lobeneath | 67.82.86 |
| prodrifter72 | 66.75.124 |
| RbrtMackay | 81.77.108 |
| revrendpoe | 65.43.0 |
| rockey | 62.64.135 |
| rockey | 80.225.68 |
| stwgolfer | 64.221.53 |
| thebear | 205.188.208 |
| thebear | 205.188.209 |
| tooms | 63.225.249 |
| ulyanov | 12.5.196 |
| WHATSUP | 172.173.81 |
| [EMAIL PROTECTED] | 80.143.42 |
| xmartyx | 68.5.149 |
| xym180 | 216.41.132 |
| zook10 | 213.89.57 |
+---------------------------+-------------+
59 rows in set (0.03 sec)
As you can see, I still end up with records from the 'exclude_log'
table.. ?
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
