I guess you need something like
SELECT * FROM password_log t1 LEFT OUTER JOIN exclude_log t2 ON
t1.remote_host=t2.ip_block WHERE t2.ip_block IS NULL;
I am just guessing that both tables are related through remote_host and
ip_block,
Adolfo
On Thu, 2002-12-19 at 17:31, Eric Anderson wrote:
> 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
>
--
Adolfo Bello <[EMAIL PROTECTED]>
---------------------------------------------------------------------
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
