RE: Can this query be done w/o adding another column?

[Travis Ard]

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'