Greetings experts,
I am really confused about the results I am getting when I run the following query:
SELECT Name, COALESCE(NULLIF(workemail, ''), homeemail) AS empEmail
FROM MasterList ml
WHERE NOT EXISTS (
SELECT email
FROM SubmittedList sl inner join DateDetails dd on sl.employeeID = dd.employeeID
WHERE Year(dd.datecreated) = Year(getDate()) and sl.email IN (ml.workemail, ml.homeemail))
ORDER BY Name ASC
First, some background. We were given what we call a master list of employees who have been tasked with providing some useful feedback by taking a survey.
This list contains a total of 340 employees.
We loaded this list into our database called MasterList
Then we have a web application that records the names, email addresses and phone numbers of the employees who have taken this survey and stores this information in a table called SubmittedList.
Finally, we are asked to provide weekly list of those who have taken the survey.
From a logical point of view, the only relationship between the MasterList and SubmittedList is email address.
The MasterList has two columns, workEmail and HomeEmail.
The query above looks at WorkEmail column first. If the employee is using his/her work email, grab that.
Otherwise, grab the home email.
Both WorkEmail and HomeEmail columns cannot both be blank at same time.
The query compares the emails on the SubmittedLit table with the emails on the MasterList table.
If there is a match, then an employee has submitted the survey.
If there is no match, then the employee has not submitted the survey.
Then the list of the employees who have not submitted the survey is to be sent to management.
The issue we are having currently, is when the first part of the query that checks list of records based on existence of either workemail or homeemail, I get the correct result, say for instance 340 records.
SELECT Name, COALESCE(NULLIF(workemail, ''), homeemail) AS empEmail
FROM MasterList ml
When I run the bottom half of the query based on the existence of email addresses within the range date range of submission, I get the correct results, for instance, 220.
SELECT email
FROM SubmittedList sl inner join DateDetails dd on sl.employeeID = dd.employeeID
WHERE Year(dd.datecreated) = Year(getDate()) and sl.email IN (ml.workemail, ml.homeemail))
However, when I run the query I posted at the beginning, the total number of those who have not taken the survey shows 20 more users have taken the survey.
We could not find any duplications.
Can you please tell me if there is something wrong with my query comparing the difference between SubmittedList and MasterList based on the existence of email addresses on both tables?
Did I approach the query incorrectly?
Thank for your assistance in advance