Paul,
I think you could accomplish this by adding a subquery to your where clause,
like:
AND NOT EXISTS (SELECT * FROM mappings m where m.src_ip=src_ip)
Hope that helps.
Nathan
On Tue, Oct 12, 2010 at 03:19:36AM -0700, Paul Halliday wrote:
I have 2 tables: events and mappings.
what I
I would try:
SELECT DISTINCT(e.src_ip)
FROM event AS e
LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%'
AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%'
AND INET_NTOA(e.src_ip) NOT LIKE
On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers joh...@pixelated.netwrote:
I would try:
SELECT DISTINCT(e.src_ip)
FROM event AS e
LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
AND INET_NTOA(e.src_ip) NOT LIKE
You may get better performance from your query, and be able to make better
use of indexes if you use integer comparisons for your IP address
expressions instead of converting to strings with pattern matching. You
might consider something like the following:
SELECT DISTINCT(e.src_ip)
FROM event
Absolutely was just going to suggest this Travis.
Another option and this is untested, but is to use HAVING and an alias.
Something to this effect...
SELECT INET_NTOA(e.src_ip) AS source_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
HAVING source_ip BETWEEN
