I have three tables, Employees table, DateDetails table, and AuthorizedUsers table.
The Employees table has email address among other fields but our interest on this table is email address.
Then Employees table also has employeeID as the PK.
DateDetails table has a date field name called DateCreated which stores date values whenever a record is added to the Employees table.
DateDetails table is related to Employees table by employeeID
Finally, we have AuthorizedUsers table.
This table contains a list of users who are authorized to submit data to the Employees table and DateDetails table.
We have been tasked with sending weekly email reminders to those AuthorizedUsers who have not submitted their information to the Employees and DateDetails tables.
The only logical approach that I could come up with is to try compare the email addresses (work_email, home_email) on the AuthorizedUsers table to the email address (email) on Employees table.
Select only those records from AuthorizedUsers table where the email addresses on this table do not exist on Employees table for the current year (in this case 2023).
The records picked up from AuthorizedUsers table based on the above WHERE predicates will then be sent email reminders.
Here is the code I am using and it looks simple enough:
SELECT DISTINCT name, work_email, home_email FROM AuthorizedUsers, Employees, DateDetails WHERE Employees.employeeID = DateDetails.employeeID AND Year(dateCreated) = Year(getDate()) AND work_email <> email OR home_email <> email ORDER By name DESC
The issue that I am having with this code is that if I don't use the DISTINCT keyword, it generates over 32,000 records.
However, if I use DISTINCT, it gives the accurate number which is 297.
Any ideas what is wrong with my code?
Thanks a lot in advance