List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:December 9 2009 12:29pm
Subject:Join on a where clause.
View as plain text  
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.
Thread
Join on a where clause.Paul Halliday9 Dec
  • RE: Join on a where clause.Neil Aggarwal9 Dec
    • Re: Join on a where clause.Joerg Bruehe9 Dec
      • RE: Join on a where clause.Neil Aggarwal9 Dec
      • Re: Join on a where clause.Paul Halliday10 Dec
        • Re: Join on a where clause.Andy Wallace10 Dec
        • Re: Join on a where clause.Joerg Bruehe12 Dec
  • Re: Join on a where clause.Shawn Green12 Dec