Sorry, try changing the column mappings.ip to use the table aliases (m.ip and m2.ip).
-Travis From: Paul Halliday [mailto:[email protected]] Sent: Tuesday, October 12, 2010 11:37 AM To: Travis Ard Cc: [email protected] Subject: Re: Can this query be done w/o adding another column? On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard <[email protected]> wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN "2010-10-12 03:00:00" AND "2010-10-13 03:00:00" GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause'
