From: Paul Halliday
Date: December 9 2009 12:29pm
Subject: Join on a where clause.
List-Archive: http://lists.mysql.com/mysql/219607
Message-Id: <2dab70a30912090429g6850db52r464aa1f81506e125@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1

I have 2 tables:

1) Event Data
2) Mappings

The query should return something like this:

Hits      IP                      Country Code
20        213.136.52.29     SE

I am trying this:

SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN
'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER
BY count DESC LIMIT 20;

Am I supposed to do a join somewhere? Do joins even apply in a where
clause? or am I totally off the mark.

Singularly, the queries look like this:

SELECT cc FROM mappings WHERE INET_ATON('src_ip') BETWEEN start_ip AND end_ip;

SELECT COUNT(src_ip) AS count, INET_NTOA(src_ip) FROM event WHERE
timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00' GROUP
BY src_ip ORDER BY count DESC LIMIT 20;


Thanks.