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

Time:11-10

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.

CodePudding user response:

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.

CodePudding user response:

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
)
  • Related