phpmyadmin Question posted in
The official documentation can be found here.

Selecting rows with unique column values – Phpmyadmin

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
[email protected]  GB             track openers
[email protected]  GB             redirect
[email protected]  GB             track openers
[email protected]  GB             redirect
[email protected]  GB             track openers
[email protected]  GB             redirect
[email protected]  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');
Tags:

3

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    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'
);
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search