Lua_learner
Lua_learner

Reputation: 45

SQL check across rows

Say I have a table structure below. I am looking to find only those IPs that have made just the HEAD request. In this case IP6.

mysql> select distinct ip, method from request_records;
+-------+---------+
| ip    | method   |
+-------+---------+
| IP1   | GET     |
| IP1   | POST    |
| IP1   | OPTIONS |
| IP1   | HEAD    |
| IP2   | GET     |
| IP2   | POST    |
| IP2   | OPTIONS |
| IP2   | HEAD    |
| IP3   | GET     |
| IP4   | POST    |
| IP5   | OPTIONS |
| IP6   | HEAD    |
+-------+---------+

Using the NOT IN condition returns other IPs which is incorrect (seen below). How do I check across multiple rows

mysql> select distinct ip,method from request_records where method not in ('GET','POST','OPTIONS');
+-------+-------+
|  ip   | method |
+-------+-------+
| IP1   |  HEAD  |
| IP2   |  HEAD  |
| IP6   |  HEAD  |
+-------+-------+

Upvotes: 0

Views: 51

Answers (2)

fifonik
fifonik

Reputation: 1606

I would not use aggregation for this situation (they are too inefficient in this case and as I expect it might be quete a lot of records in the table).

So, I'd use something like this:

-- explain
SELECT 
    t1.ip
FROM
    request_records AS t1
    LEFT JOIN request_records AS t2 ON (
            t2.ip = t1.ip
        AND t2.method != t1.method
    )
WHERE
        t1.method = 'HEAD'
    AND t2.ip IS NULL

indexes (ip, method) and (method, ip) are recommended.

Explain results:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  t1  NULL    ref IX_method_ip    IX_method_ip    17  const   3   100.00  Using index
1   SIMPLE  t2  NULL    ref IX_method_ip,IX_ip_method   IX_ip_method    17  t1.ip   2   100.00  Using where; Not exists; Using index

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can use aggregation:

select ip
from request_records
group by ip
having min(request) = max(request) and min(request) = 'HEAD';

If you have a separate table of unique ips, then I would recommend not exists:

select ip
from ips
where not exists (select 1
                  from request_record rr
                  where rr.ip = ips.ip and
                        rr.request <> 'HEAD'
                 );

Of course, this will return rows with no 'HEAD' -- which may or may not be what you want. You can add:

select ip
from ips
where not exists (select 1
                  from request_record rr
                  where rr.ip = ips.ip and
                        rr.request <> 'HEAD'
                 ) and
      exists (select 1
              from request_record rr
              where rr.ip = ips.ip and
                    rr.request = 'HEAD'
             );

Upvotes: 1

Related Questions