CODEWITHSUNDEEP
sqlt-sql
madcolor
madcolor

Reputation: 8170

SQL select where repeating, but distinct on another column

I have the following table: 

uid | key | email
-----------------
1   |  1  | [email protected]
2   |  2  | [email protected]
3   |  3  | [email protected]
4   |  4  | [email protected]
5   |  4  | [email protected]
6   |  4  | [email protected]
7   |  6  | [email protected]
8   |  7  | [email protected]

I'd like to grab all of the rows with distinct key but repeating email values

the result should look like:

uid | key | email
-----------------
1   |  1  | [email protected]
2   |  2  | [email protected]
3   |  3  | [email protected]

Upvotes: 0

Views: 3071

Answers (2)

Dave Swersky
Dave Swersky

Reputation: 34810

SELECT * FROM table
WHERE email NOT IN
(
SELECT email 
FROM table GROUP BY email
HAVING COUNT(email) <= 1)
AND key IN
(
SELECT key
FROM table
GROUP BY key
HAVING COUNT(key) = 1 
)

Upvotes: 1

Joshua Shannon
Joshua Shannon

Reputation: 2731

SELECT MIN(uid) as uid, key, email
FROM Keys k INNER JOIN 
    (SELECT email FROM KEYS GROUP by email HAVING COUNT(email) > 1 ) k2
    ON k.email = k2.email
GROUP BY key, email
HAVING COUNT(key) = 1

Upvotes: 2

Related Questions