CODEWITHSUNDEEP
sqlmysql
jmccartie
jmccartie

Reputation: 4976

MySQL: Find duplicate users WHERE item count < 1

Trying to find duplicate users by email, who have an item count less than 0. I've got the duplicate users working (though it returns the full list of users sorted, instead of a subset):

select users.id, email, count(email) as count
from users
group by users.email
order by count desc

I'm trying to join on Items where count(items.id) < 1, but that doesn't seem to work. 

select users.id, email, count(email) as count
from users
join items on items.user_id = users.id
having count(items.id) < 1
group by users.email
order by count desc

I also tried an IN query, but can't seem to get the syntax right.

Easy way to do this? Thanks!

UPDATE:

This query did the trick:

    select DISTINCT(u1.id), u1.email
    from users u1
    inner join users u2
        on 1=1
        and u1.email = u2.email
        and u1.id != u2.id
    where not exists (select * from items i where i.user_id = u1.id)
    order by u1.id

Upvotes: 0

Views: 1863

Answers (2)

LukLed
LukLed

Reputation: 31842

Duplicated users:

select 
    email, 
    count(*) as count,
    min(id) first_user_with_this_email_id
from users
group by email
having count(*) > 1

For second one, try this:

select 
    users.email, 
    count(*) as count
from users
left join items 
on (items.user_id = users.id)
where items.id is null --there are no related items
group by users.email
having count(*) > 1 --there are at least two users

Another version of second:

select 
    u.email, 
    count(*) as count
from users u
where not exists (select * from items i where i.user_id = u.id)
group by u.email
having count(*) > 1 --there are at least two users

Make sure you have index on user_id in items table.

Upvotes: 2

DhruvPathak
DhruvPathak

Reputation: 43235

try using WHERE instead of "having". May be this link helps: http://www.devx.com/DevX/Tip/21295

Modified query would be :

select users.id, email, count(email) as count
from users
join items on items.user_id = users.id
where count(items.id) < 1
group by users.email
order by count desc

[ did not run and check the query, just a correction ]

Upvotes: -1

Related Questions