Hi everybody!
Neil Aggarwal wrote:
> Paul:
>
>> 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;
I am surprised by the quotes you have around the "start_ip" and "end_ip"
columns; to me, this makes that look like strings.
From your posting, I see the result you hope to get but not the one you
actually get. IMO, just dropping the single quotes around the two column
names should produce the data you want to get.
Or what is the result you receive?
>
> Hmm.. The hard part is that your mappings
> table is not a list of all IP addresses.
> It has a range from start to end, but the
> actual IP in the event table is not listed
> there.
>
> Joins require a column value from each table
> to match. That is not the case for you.
A matching column is called an "equijoin" (from "equality") in SQL
slang, and this is the most common form of a join. (Also, it is the
fastest, if there are suitable indices which can be used.)
However, that is not mandatory / the only form.
You can have a join with any predicate combining columns of the (two)
involved tables.
You can even have a join without any such predicate, which means every
combination of any two rows of the tables is to be returned. This is
known as "Cartesian Product" and is in most cases not what you want.
>
> I think you are going to have to do this in
> your application code.
I never dealt with the assignment of IP addresses to countries.
As long as the problem can be solved using ranges (or multiple ranges)
which do not overlap, the join should solve it.
Regards,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
(+49 30) 417 01 487
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
