CODEWITHSUNDEEP
mysqlsql
Martijn Ebbens
Martijn Ebbens

Reputation: 524

Finding ids that have not yet been assigned in another table

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.

bots

enter image description here

proxies

enter image description here

I basically need the ids of 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;

Upvotes: 1

Views: 31

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Another way to do this is to use NOT IN:

SELECT *
  FROM PROXIES p
  WHERE p.ID NOT IN (SELECT b.PROXY_ID
                       FROM BOTS b)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562330

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would recommend NOT EXISTS:

SELECT p.*
FROM proxies p
WHERE NOT EXISTS (SELECT 1
                  FROM bots b
                  WHERE b.proxy_id = p.id
                 );

Basically, this says "Get me all proxies that have no bot with that proxie id".

Upvotes: 2

Related Questions