Question 229840 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql missing email address query

I have a table (datas):

line account name email
1 1000 Reyes VonRueden Sr. [email protected]
1 1000 Dr. Elena Bartell V
1 1000 Lucio Wehner
2 1000 April Hansen
2 1000 Amy Waelchi [email protected]
2 1000 Tremaine Cassin
3 1000 Susana White Jr. [email protected]
3 1000 Roxane Hoeger
3 1000 Prof. Eric Boehm
4 1000 Miles Considine ** **
4 1000 Araceli Effertz ** **
4 1000 Prof. Adolph Pacocha ** **
5 1000 Prof. Rafaela Hills [email protected]
5 1000 Delta Becker
5 1000 Aditya Ratke [email protected]
6 1000 Miss Elaina Aufderhar Sr. [email protected]
6 1000 Miss Stephanie Russel IV
6 1000 Dr. Ramiro Marks IV [email protected]
7 1000 Garfield Kozey ** **
7 1000 Gene Hilpert ** **

The line and account fields clearly define a customer.
The name field is a worker.
I am looking for customers (line+account) whose name field does not have an email address.
In this case the results are: 4-1000 and 7-1000.

I can solve it with 2 queries and using excel, but it would be faster in mysql.

thanks if you help 😉

Tags:

3

Answers


  1. 0 
       SELECT line, account
      FROM customer
     WHERE email NOT LIKE '%@%'
   GROUP BY line, account

    As every email should have the @ symbol, you should exclude all entries with emails. It is less performant than WHERE email IS NULL though (won’t matter though until you go through A LOT of data). You should make sure that your tables are clean, fields only filled with valid data or nothing, allowing you to use queries not relying on substring search where possible.

    Anyway the expected result would be:

    line account
    4 1000
    7 1000
    Login or Signup to reply.
  2. 0

    Here is the query you’re looking for.

    SELECT * FROM customer WHERE email IS NULL or email = '';

    This query will give you all fields that do not have an email.

    If you’re however looking for records that do not have a valid email address, then use the query below.

    SELECT * FROM customer WHERE email not like '%@%';
    Login or Signup to reply.
  3. 0

    To accomplish this, utilize a SQL JOIN statement. Here is a search that ought to produce the outcomes you need:

    SELECT DISTINCT data.line, data.account
FROM datas data
LEFT JOIN datas data2 ON data.line = data2.line AND data.account = data2.account AND data2.email IS NOT NULL
WHERE data.email IS NULL AND data2.email IS NULL;

    hope this is helpful!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search