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

user_16763219 0
Viewed 58

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.

sql-null having max group-by mysql
2 Answers

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.

user_104988280

You can use NOT EXISTS like this:

SELECT * FROM table a WHERE NOT EXISTS(
    SELECT 1 FROM table b 
    WHERE a.Manager_Name = b.Manager_Name AND Email_Address IS NOT NULL
)
user_57309490
Related