mysql query for getting all items and removing one item based on a criteria

Question

so here's what I accomplished right now:

1. given email1 and email2,
2. get all emails that are not equal to email1
3. create a list of them
4. check if email2 exists in the list
5. if it does return false, if it does not return true

Is there a way to accomplish this via a query instead of creating a list and searching if email2 exists in it? (because I've implemented the above pseudo in PHP and I had a query object and an array...ugly approach)

given a user table with the following schema id INT(11) PRIMARY KEY email VARCHAR(100)

Answer 1

The query:

SELECT id
FROM sometable
WHERE email=:email2

Just see if it returns any rows.

This won't work if email1 is equal to email2, but if that were the case then it wouldn't be worth running the query in the first place.

Answer 2

Hello,

Here is a simple query to get the result you need (0=false, 1=true):

select
    case when count(1) > 0 then 0 else 1 end as result
from 
    email_table
where 
    email != email1
and email  = email2