CODEWITHSUNDEEP
mysql
AndrewTsang
AndrewTsang

Reputation: 316

MySQL select distinct with no duplicates

I'm trying to select only unique emails from my list. Here's an example of my database

1 [email protected] unpaid newspaper
2 [email protected] paid magazine
3 [email protected] unpaid magazine
4 [email protected] paid magazine

the problem is, if I use SELECT DISTINCT WHERE payment=paid, john would appear. When I do payment=unpaid, john would also appear. I only want to select customers with a good payment history (no unpaids). How do I sort that out?

Upvotes: 2

Views: 80

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can also use sum with expression payment = 'unpaid' to get the emails who don't have any unpaid entries

select email
from table1
group by email
having (sum(payment = 'unpaid')) = 0

DEMO

Upvotes: 1

Jens
Jens

Reputation: 69440

Try not in with subselect:

select * from `table` where payment='paid' and user not in (select user from `table` where payment='unpaid')

Upvotes: 0

Mureinik
Mureinik

Reputation: 311063

You can use the exists operator:

SELECT DISTINCT email
FROM   mytable a
WHERE  NOT EXISTS (SELECT *
                   FROM   mytable b
                   WHERE  a.email = b.email AND b.payment = 'unpaid')

Upvotes: 0

Related Questions