From: Paul Halliday
Date: October 12 2010 5:36pm
Subject: Re: Can this query be done w/o adding another column?
List-Archive: http://lists.mysql.com/mysql/223294
Message-Id: <AANLkTiknGQF4M0BHu359z-Tht2XhyuuZU8==79UzyWna@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=20cf30334fcb7da0f004926eec3a

--20cf30334fcb7da0f004926eec3a
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard <travis_ard@stripped> 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'

--20cf30334fcb7da0f004926eec3a--