Advanced MySQL grouping query

Question

I have an online store, with an order DB something like this:

id  purchase_date  buyer_name  buyer_email  product_name  delivery_status
1   10.09.2014     jo smith    jo@smith.com dildo         delivered
2   10.09.2014     jo smith    jo@smith.com superdildo    delivered
3   11.09.2014     john lol    jo@lol.com   cream         delivered
4   13.09.2014     john lol    jo@lol.com   supercream    not delivered
5   15.09.2014     john doe    john@doe.com lingerie      delivered
6   15.09.2014     john doe    john@doe.com lingerie2     not delivered
7   15.09.2014     feels no    ff@trol.com  supercream    delivered
8   18.09.2014     jo smith    jo@smith.com cream         not delivered

I want to select all distinct buyer_email's from this table, where all the customers orders on that day are "delivered".

By this i mean:

ID 1 and 2 would be a match, and the query should output jo@smith.com, because both orders he did on that day are delivered.

ID 3 would also be a match, cause all orders that jo@lol.com placed on that day (11.09.2014) are delivered.

ID 4 would not be matched in the query (not all orders on that day on that name are delivered)

ID 5 and 6 would not be matched either.

ID 7 is a match

ID 8 is not a match.

Answer 1

You want an aggregation with a having clause. At least to start. The following gets days and buyers that have fully delivered orders:

select o.purchase_date, o.buyer_email
from orders o
group by o.purchase_date, o.buyer_email
having sum(delivery_status <> 'delivered') = 0;

If you want the order ids, the easiest way is to use group_concat():

select o.purchase_date, o.buyer_email, group_concat(o.id) as ids
from orders o
group by o.purchase_date, o.buyer_email
having sum(delivery_status <> 'delivered') = 0;

If you want the full rows, you can use a join.

Answer 2

select distinct buyer_email
from your_table
group by buyer_email, purchase_date  
having sum(delivery_status <> 'delivered') = 0