Query to Select Only Records from AuthorizedUsers table where email address does not exist in Employees table does not give correct results

Transact-SQL
1

Greeting again,

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

2

You want to do this:

SELECT *
FROM AuthorizedUsers au
WHERE (some filter)

Now, what should that filter be?
Do you want those AuthorizedUsers whose work_email and home_email are not found in the Employees table?

SELECT *
FROM AuthorizedUsers au
WHERE NOT EXISTS (
                  SELECT *
                  FROM Employees e 
                  WHERE e.email IN (au.work_email, au.home_email)
                 )

For each row in AuthorizedUsers, the correlated subquery will be run.
If any matching rows are found, the NOT EXISTS will return false, and the row from AuthorizedUsers will not be returned in the result.
If there are no matching rows found, NOT EXISTS will return true, and the row from AuthorizedUsers will be returned in the result.

From here, you can add on or make changes to suit any other conditions you have.

3

Great stuff; works like a dream.
Thank you so much SqlHippo (love that name).

All I had to do was add the join to dateDetails table because we wanted to query records for the current year.

Thank again for your prompt response and awesome solution.