MySQL - Interesting search I'm having issues with

Question

I am trying to find a way to pull specific data with only a MySQL query. I have a database as follows:

<pre>
id     |     number     |   eventdate  |     type     |     email     |
-----------------------------------------------------------------------
1      | 1000           | 2014-01-01   | 5            | tom@email.com |
2      | 1001           | 0000-00-00   | 1            | tom@email.com |
3      | 1002           | 2014-01-01   | 3            | john@email.com|
4      | 1003           | 0000-00-00   | 6            | joe@email.com |
5      | 1004           | 2014-01-01   | 3            | bob@email.com |
6      | 1005           | 0000-00-00   | 1            | adam@email.com|
7      | 1006           | 2014-01-01   | 3            | beth@email.com|
</pre>

The query is to find anyone that does not have a type of 1, by but does have a eventdate that is not equal to '0000-00-00'. But where there are duplicate email addresses, we only want to pay attention to the address that does not have the eventdate. Make sense?

Nothing urgent, just a question someone asked us to pull data for. We managed to do it the longer way of just manually pulling data out... But, there has to be an easier way. I think we are just trying to make it more difficult than it has to be...

Thanks for any tips!

(note the actual table is much larger,has many duplicate rows, and many more columns. These are just the columns that we are interested in.)

Answer 1

You can do this readily with group by and having:

select email
from table t
group by email
having sum(type = 1) = 0 and
       sum(eventdate <> '0000-00-00') > 0;

Each clause in the having clause tests one of your conditions.