CODEWITHSUNDEEP
mysqlsql
Volatil3
Volatil3

Reputation: 14988

Unable to filter out records

I have two tables:

proxies_meta

proxies

proxies_meta is like the place where all ip addresses reside while proxies gets an entry or gets updated when an ip from proxies_meta is used.

Now at given day I have to pick a proxy IP which have not been banned yet that is status = 0 and hits does not cross 10 per day. So far I tried this but not working

select pm.ip_address as IP,sum(p.hits) as total_hits
from proxies_meta as pm
LEFT JOIN proxies as p
ON p.ip_address = pm.ip_address
AND pm.`current_status` = 0
AND date(p.updated_at) = '2017-06-23'
GROUP BY IP
having total_hits < 11 OR total_hits is NULL

This query works as long as status is 0 or there is no record in proxies. If current_status and status is changed from 0 to -1 then still it picks because of LEFT JOIN

How do I make sure that I don't get the unwanted IP Address and it also checks total hits per day?

Upvotes: 1

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

I think you only need to move condition on current status needs to the where clause:

select pm.ip_address as IP, sum(p.hits) as total_hits
from proxies_meta pm left join
     proxies p 
     on p.ip_address = pm.ip_address and
        date(p.updated_at) = '2017-06-23'
where pm.current_status = 0
group by pm.ip_address
having total_hits < 11 or total_hits is null;

A left join keeps all rows in the first table, regardless of whether the on clause evaluates to true, false, or NULL. So, conditions on the first table don't filter anything. For actually filtering, conditions on the first table need to be in the where clause.

Upvotes: 1

Related Questions