CODEWITHSUNDEEP
mysqlselect
John
John

Reputation: 7067

MySQL return result if value is empty in another table

I have 2 mysql tables "users1" and "users2". They both have the following fields

users1: email, zipcode, first_name, last_name, processed
users2: email, zipcode, first_name, last_name

I have the following SQL query:

SELECT zipcode FROM users1 WHERE processed IS NOT NULL

Currently this query returns the result from users1 table. However, Before returning the result, I would like to check that "zipcode" DOES NOT EXIST in the users2 table.

So basically, it needs to only return the result if the returned zipcode from "users1" does not exist in "users2"

Any idea how I can do this?

Thanks

Upvotes: 0

Views: 813

Answers (3)

Bohemian
Bohemian

Reputation: 425013

Use an outer join filtering to get only rows not present in the other table:

SELECT u1.zipcode
FROM users1 u1
LEFT JOIN users2 u2 ON u2.zipcode = u1.zipcode
WHERE processed IS NOT NULL
AND u2.zipcode IS NULL

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18747

You can use NOT IN:

SELECT zipcode
FROM users1 
WHERE processed IS NOT NULL
  AND zipcode NOT IN
       (SELECT distinct zipcode from users2)

Upvotes: 1

Jens
Jens

Reputation: 69440

Try this untested query:

select user2.zipcode from user2 where user2.zipcode not in (SELECT zipcode FROM users1 WHERE processed IS NOT NULL)

Upvotes: 0

Related Questions