List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:October 13 2010 10:45am
Subject:Re: Can this query be done w/o adding another column?
View as plain text  
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_ard@stripped> wrote:

> Sorry, try changing the column mappings.ip to use the table aliases (m.ip
> and m2.ip).
>
>
>
> -Travis
>
>
>
> From: Paul Halliday [mailto:paul.halliday@stripped]
> Sent: Tuesday, October 12, 2010 11:37 AM
> To: Travis Ard
> Cc: mysql@stripped
> Subject: Re: Can this query be done w/o adding another column?
>
>
>
> 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'
>
>


-- 
Paul Halliday
Ideation | Individualization | Learner | Achiever | Analytical
http://www.pintumbler.org

Thread
Can this query be done w/o adding another column?Paul Halliday12 Oct
  • RE: Can this query be done w/o adding another column?Travis Ard12 Oct
    • Re: Can this query be done w/o adding another column?Paul Halliday12 Oct
      • RE: Can this query be done w/o adding another column?Travis Ard12 Oct
        • Re: Can this query be done w/o adding another column?Paul Halliday13 Oct