CODEWITHSUNDEEP
mysqlsql
Neil
Neil

Reputation: 5178

Return Only Duplicate Records After Inner Join

I have a users table and an emails table. A user can have many emails.

I want to grab only those users that have more than one email. Here is what I have so far:

SELECT Users.name, emails.email
FROM Users
INNER JOIN emails
On Users.id=Emails.user_id
/*specify condition to only grab duplicates here */

Upvotes: 0

Views: 46

Answers (4)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

You have to count for the total number of emails in a seperate subquery that returns the user_id of those users having more than one emails.

SELECT users.name, emails.email
FROM users
INNER JOIN emails
ON users.id = emails.user_id
INNER JOIN (
    SELECT user_id
    FROM emails
    GROUP BY user_id
    HAVING count(*) > 1
) _cc
ON users.id = _cc.user_id;

UPDATE: If you only need the user_id without the additional emails you can use this:

SELECT DISTINCT(emails.user_id)
FROM users
INNER JOIN emails
ON users.id = emails.user_id
INNER JOIN (
    SELECT user_id
    FROM emails
    GROUP BY user_id
    HAVING count(*) > 1
) _cc
ON users.id = _cc.user_id;

Lets say we have the data below:

USERS
id name
1  A
2  B
3  C
4  D
5  E

EMAILS
id user_id email
1  1       email 1
2  1       email 2
3  2       email 3
4  2       email 2
5  3       email 5

So using the above data the results of the two queries will be:

QUERY 1

name email
A    email 1
A    email 2
B    email 3
B    email 4

QUERY 2

user_id
1
2

Upvotes: 0

John S
John S

Reputation: 33

Try this. You are grouping by emails and showing those that have a count > 1

SELECT Users.name, emails.email
FROM Users
INNER JOIN emails
On Users.id=Emails.user_id 

GROUP BY emails.email HAVING COUNT(*) > 1

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT u.id
FROM Users u
INNER JOIN emails e On u.id = e.user_id
group by u.id 
having count(distinct e.email) > 1

Use group by and having

Upvotes: 3

J.S.Orris
J.S.Orris

Reputation: 4821

You can also use a CTE:

;WITH CTE AS (
SELECT Users.name, emails.email, ROW_NUMBER() OVER (PARTITION BY emails.email ORDER BY emails.email) AS 'Rank' 
FROM Users
INNER JOIN emails
On Users.id=Emails.user_id)
SELECT * FROM CTE WHERE Rank > 1

Upvotes: 0

Related Questions