After bashing at this for a while with no luck I replaced the "inner" with "left" and I got the desired result.
Thanks for the help. On Tue, Oct 12, 2010 at 5:18 PM, Travis Ard <travis_...@hotmail.com> wrote: > Sorry, try changing the column mappings.ip to use the table aliases (m.ip > and m2.ip). > > > > -Travis > > > > From: Paul Halliday [mailto:paul.halli...@gmail.com] > Sent: Tuesday, October 12, 2010 11:37 AM > To: Travis Ard > Cc: mysql@lists.mysql.com > Subject: Re: Can this query be done w/o adding another column? > > > > On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard <travis_...@hotmail.com> > 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' > > -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
