Re: Mysql SELECT question (LEFT JOIN?)

2002-11-15 Thread John Ragan 

by now, i hope, you've gotten answers from the 
sql gurus on the list, so i won't clutter with my 
humble attempts.

your comment about problems with joins indicates 
that corereader might be of some help to you if 
you have a windows box for a front end.  it will 
let you do quick point-and-click queries, so you 
can experiment with fairly complex joins.

download it from http://corereader.com

it's intended to be a production system, but it's 
a great teaching tool.  it connects to anything, 
but it especially likes mysql.


 
 Assume two tables:
 
 CREATE TABLE block_ip (
   datestamp int(11) NOT NULL default '0',
   remote_addr char(15) NOT NULL default '',
   PRIMARY KEY  (remote_addr),
   KEY datestamp (datestamp)
 ) TYPE=MyISAM;
 
 CREATE TABLE brute_force (
   datestamp int(11) NOT NULL default '0',
   remote_addr char(15) NOT NULL default '',
   remote_user char(35) NOT NULL default '',
   KEY remote_addr (remote_addr),
   KEY datestamp (datestamp),
   KEY remote_user (remote_user)
 ) TYPE=MyISAM;
 
 Contents of the 'brute_force' table (remote_addr):
 
 1.2.3.4
 2.3.4.5
 3.4.5.6
 4.5.6.7
 5.6.7.8
 6.7.8.9
 
 Contents of the 'block_ip' table (remote_addr):
 
 2.3.4.5
 4.5.6.7
 
 Can someone help me with the query that will select all the
 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip'
 table?
 
 Something like:
 
 select brute_force.* from brute_force, block_ip where
 brute_force.remote_addr != block_ip.remote_addr
 
 maybe?  I have a feeling it's some sort of left join, and I was never
 very good at those.  :-/
 
 
 



-- 
John Ragan
[EMAIL PROTECTED]
http://www.CoreReader.com/ 


-
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

re: Mysql SELECT question (LEFT JOIN?)

2002-11-15 Thread Victoria Reznichenko 
Eric,
Friday, November 15, 2002, 1:36:54 AM, you wrote:

EA Assume two tables:

EA CREATE TABLE block_ip (
EA   datestamp int(11) NOT NULL default '0',
EA   remote_addr char(15) NOT NULL default '',
EA   PRIMARY KEY  (remote_addr),
EA   KEY datestamp (datestamp)
EA ) TYPE=MyISAM;

EA CREATE TABLE brute_force (
EA   datestamp int(11) NOT NULL default '0',
EA   remote_addr char(15) NOT NULL default '',
EA   remote_user char(35) NOT NULL default '',
EA   KEY remote_addr (remote_addr),
EA   KEY datestamp (datestamp),
EA   KEY remote_user (remote_user)
EA ) TYPE=MyISAM;

EA Contents of the 'brute_force' table (remote_addr):

EA 1.2.3.4
EA 2.3.4.5
EA 3.4.5.6
EA 4.5.6.7
EA 5.6.7.8
EA 6.7.8.9

EA Contents of the 'block_ip' table (remote_addr):

EA 2.3.4.5
EA 4.5.6.7

EA Can someone help me with the query that will select all the
EA 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip'
EA table?

EA Something like:

EA select brute_force.* from brute_force, block_ip where
EA brute_force.remote_addr != block_ip.remote_addr

EA maybe?  I have a feeling it's some sort of left join, and I was never
EA very good at those.  :-/

Yes, you need LEFT JOIN :)

SELECT brute_force.* FROM brute_force
LEFT JOIN block_ip ON brute_force.remote_addr=block_ip.remote_addr
WHERE block_ip.remote_addr IS NULL;



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
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