2

I have the following MySQL query. 

SELECT
    login,
    firstname,
    lastname,
    email
FROM
    xcart_customers
WHERE
    login LIKE 'anonymous%'
AND email NOT IN (
    SELECT
        email
    FROM
        xcart_customers AS cx
    WHERE
        cx.login NOT LIKE 'anonymous%'
)
GROUP BY
    email;

Basically there are two sets of customers, customers that have logins, and anonymous customers who all start with a login of 'anonymous%'.

I am trying to remove non-anonymous users from the list, that have the same email address as the anonymous users.

I thought the above query would have worked, but I still get some emails addresses that match non-anonymous users.

login           |  firstname     |   lastname     |   email
---------------------------------------------------------------------------
anonymous-10    |  Eric          |   Byorn        |   byorn@mail.com
---------------------------------------------------------------------------
some_user_name  |  Eric          |   Byorn        |   byorn@mail.com
---------------------------------------------------------------------------

So I am trying to solve, all anonymous users, who only appear in the anonymous results.

DevelumPHP
  • 155
  • 1
  • 8

1 Answers1

1

The most efficient, and IMHO elegant, solution is to use an outer join:

SELECT
  a.login,
  a.firstname,
  a.lastname,
  a.email
FROM xcart_customers a
LEFT JOIN xcart_customers b ON b.email = a.email
  AND b.login NOT LIKE 'anonymous%'
WHERE a.login LIKE 'anonymous%'
AND b.email IS NULL

There's two key tricks in play here:

  1. Because missed LEFT JOINs return all NULLs in the left joined table, the WHERE clause excludes rows that have a match
  2. All conditions (even non-key) on the joined table must be in the join condition, ie not in the WHERE clause, otherwise to efficively turn the outer join into an inner one

Note that your use of GROUP BY is both flawed and unnecessary.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Except that I still get duplicate email addresses in the results, if there are multiple anonymous rows from the same checkout customer. – DevelumPHP Feb 24 '16 at 07:07
  • You didn't say anything about duplicates. Does `SELECT DISTINCT ...` work? If not, ask a new question (because this question has been answered) – Bohemian Feb 24 '16 at 07:40