List:General Discussion« Previous MessageNext Message »
From:Adolfo Bello Date:December 20 2002 1:31am
Subject:Re: Excluding records?
View as plain text  
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   |
> | barak@stripped          | 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  |
> | wolfgang-ch.kleine@stripped | 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 <mysql-thread128015@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-adolfobello=bisapi.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
-- 
Adolfo Bello <adolfobello@stripped>

Thread
Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
  • Re: Can MySQL handle 120 million records?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
    • Re: Can MySQL handle 120 million records?Michael She18 Dec
      • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
        • RE: Can MySQL handle 120 million records?Adam Nelson18 Dec
      • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
        • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
          • Re: Can MySQL handle 120 million records?Gerald Jensen18 Dec
            • Re: Can MySQL handle 120 million records?Aaron Clausen18 Dec
    • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
        • Database type questionRichard E. Perlotto II19 Dec
          • Re: Database type questionIikka Meriläinen19 Dec
      • Re[2]: Can MySQL handle 120 million records?Dyego Souza do Carmo19 Dec
    • RE: Can MySQL handle 120 million records?Wayne Lewis18 Dec
      • Excluding records?Eric Anderson19 Dec
        • Re: Excluding records?Adolfo Bello20 Dec
    • Re: Re[2]: Can MySQL handle 120 million records?Harald Fuchs19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
      • Re: Re[2]: Can MySQL handle 120 million records?Paul DuBois19 Dec
  • Re: Can MySQL handle 120 million records?Muruganandam18 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
      • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
            • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
              • Re: Can MySQL handle 120 million records?Michael She18 Dec
                • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny18 Dec
            • Re: Can MySQL handle 120 million records?Michael Bacarella18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • RE: Can MySQL handle 120 million records?Joe Stump18 Dec
              • Re: Can MySQL handle 120 million records?Michael T. Babcock18 Dec
            • Can I build a web page to extract data?Gary Hostetler18 Dec
            • Re: Can MySQL handle 120 million records?Csongor Fagyal19 Dec
          • Re: Can MySQL handle 120 million records?Michael She18 Dec
        • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Jocelyn Fournier18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
      • Re: Can MySQL handle 120 million records?W. D.18 Dec
    • Re: Can MySQL handle 120 million records?Csongor Fagyal18 Dec
Re: Can MySQL handle 120 million records?B.G. Mahesh18 Dec
  • Re: Can MySQL handle 120 million records?David T-G18 Dec
    • RE: Can MySQL handle 120 million records?JamesD18 Dec
      • Re: Can MySQL handle 120 million records?David T-G18 Dec
RE: Can MySQL handle 120 million records?Peter Vertes18 Dec
  • RE: Can MySQL handle 120 million records? - Impressive! How do you guys do that?Qunfeng Dong18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How doyou guys do that?R. Hannes Niedner18 Dec
    • Re: Can MySQL handle 120 million records? - Impressive! How do youguys do that?Michael T. Babcock18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
      • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Qunfeng Dong18 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Jocelyn Fournier19 Dec
          • Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too...Qunfeng Dong20 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Ryan Fox19 Dec
        • Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)Brent Baisley19 Dec
  • RE: Can MySQL handle 120 million records?JamesD19 Dec
    • Re: Can MySQL handle 120 million records?Jeremy Zawodny21 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
        • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
      • Re: Can MySQL handle 120 million records?Michael She21 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • Re: Can MySQL handle 120 million records?Jeremy Zawodny22 Dec
          • Re: Can MySQL handle 120 million records?Michael T. Babcock23 Dec
            • Re: Can MySQL handle 120 million records?Jeremy Zawodny23 Dec
            • DELETE with no WHERE clauseGordon23 Dec
              • Re: DELETE with no WHERE clauseDennis Salguero24 Dec
              • re: DELETE with no WHERE clauseVictoria Reznichenko24 Dec
      • RE: Can MySQL handle 120 million records?JamesD21 Dec
        • RE: Can MySQL handle 120 million records?Dean Harding21 Dec
          • RE: Can MySQL handle 120 million records?JamesD21 Dec
RE: Can MySQL handle 120 million records?David Brodbeck18 Dec
RE: Can MySQL handle 120 million records?Greg_Cope18 Dec
Re: Can MySQL handle 120 million records?Paul DuBois18 Dec
RE: Can MySQL handle 120 million records?RBRoa19 Dec
RE: Can MySQL handle 120 million records?Dana Diederich19 Dec
RE: Can MySQL handle 120 million records?John Griffin19 Dec
Re: Excluding records?King_Henree@yahoo.com20 Dec