Selecting rows with unique column values

user_12904981 0
Viewed 42

I'm trying to write an SQL command that returns emails that has the value (track openers) and doesn't have the value (redirect) for the column action_type in the same table.

email              countrycode    action_type
email1@domain.com  GB             track openers
email1@domain.com  GB             redirect
email2@domain.com  GB             track openers
email2@domain.com  GB             redirect
email3@domain.com  GB             track openers
email3@domain.com  GB             redirect
email4@domain.com  GB             track openers <<

DB Table Screenshot enter image description here

I have already tried this without success, it continues to show loading... without returning any results

SELECT DISTINCT LOWER(email) as `email`, `action_type`, `countrycode`
FROM `stats`
WHERE `email` NOT IN (SELECT `email` FROM `stats` WHERE `action_type` = 'redirect');
phpmyadmin sql
3 Answers

I would suggest aggregation:

SELECT LOWER(email) as email
FROM `stats`
WHERE action_type in ('redirect', 'track openers')
GROUP BY LOWER(email)
HAVING MIN(action_type) = MAX(action_type) AND
       MIN(action_type) = 'track openers';
user_11440350

This could also be an option. Here you look for all emails, that has action type track openers and for which doesn't exists an action with redirect action type. 

SELECT LOWER(email)
FROM stats s
WHERE s.action_type = 'track openers' AND NOT EXISTS(
    SELECT ss.email
    FROM stats ss
    WHERE s.email = ss.email
    AND ss.action_type = 'redirect'
);
user_75161340

A slightly less verbose option

SELECT LOWER(email) as email
FROM `stats`
WHERE action_type in ('redirect', 'track openers') --optional if there are only 2 types
GROUP BY email
HAVING AVG(CASE WHEN action_type ='track openers' THEN 1 END) = 1;
user_99471590
Related