I have a one-to-one relation between my webscrapers and a list of proxies. Now I’d like to know what proxies are still available to be assigned to a webscraper.
I basically need the
proxies that have not yet been assigned to a
bots row. I’ve been quite rusty with my SQL but I was thinking this would just be a simple join but performing the following join just resulted me into all rows being spat back at me;`
SELECT DISTINCT proxies.* FROM proxies JOIN bots ON bots.proxy_id <> proxies.id;
An alternative to subqueries is an outer join, and then search for cases where there is no match, i.e the outer join produced NULL in the matching table.
SELECT p.* FROM proxies AS p LEFT OUTER JOIN bot AS b ON b.proxy_id = p.id WHERE b.proxy_id IS NULL;
You can try both this solution and the subquery solution given by @GordonLinoff on this thread, and see which one performs better against your data.