Search code examples
mysqlgroup-bymaxhavingsql-null

how do i extract only the records with all email entries missing

I have a question so I have this table:

| Manager_Name | Email_Address |
| ------------ | ------------- |
| person A     |Email@email.com|
| person A     |Null|
| person A     |Null|
| person A     |Email@email.com|
| person A     |Email@email.com|
| person B     |Null|
| person B     |Null|
| person B     |Null|
| person B     |Null|
| person B     |Null|
| person C     |Email@email.com|
| person C     |Email@email.com|
| person C     |Email@email.com|
| person C     |Email@email.com|
| person C     |Email@email.com|
| person D     |Null|
| person D     |Null|
| person D     |Null|
| person D     |Null|
| person D     |Null|

My question is how do I extract only the the managers that have all the 5 email addresses missing like person B and person D, in the original table I have a lot of them I want to get only them with all 5 addresses missing.

Solution

  • You can group by Manager_Name and set the condition in the HAVING clause:

    SELECT Manager_Name
FROM tablename
GROUP BY Manager_Name
HAVING MAX(Email_Address) IS NULL;

    See the demo.