webskater - Wednesday, May 2, 2018 12:24 PM

CREATE TABLE #tblContacts
(
ContactID int,
Contact varchar(100)
)

GO

INSERT INTO #tblContacts (ContactID, Contact)
SELECT 1, 'Fred' UNION ALL
SELECT 2, 'Jim' UNION ALL
SELECT 3, 'Arthur'

GO

CREATE TABLE #tblEmails
(
EmailID int,
ContactID int,
EmailAddress varchar(100)
)

GO

INSERT INTO #tblEmails (EmailID, ContactID, EmailAddress)
SELECT 1, 1, 'fred@someorg.cam' UNION ALL
SELECT 2, 2, 'jim@goggle.cam' UNION ALL
SELECT 3, 3, 'Arthur@giggle.cam'

CREATE TABLE #tblNoEmail
(
  EmailID int
);

INSERT INTO #tblNoEmail(EmailID)
SELECT 2;

GO

SELECT *
FROM #tblContacts AS c
INNER JOIN #tblEmails AS e ON c.ContactID = e.ContactID
WHERE e.EmailID NOT IN (SELECT n.EmailID FROM #tblNoEmail AS n);

GO

DROP TABLE #tblContacts
DROP TABLE #tblEmails
DROP TABLE #tblNoEmail

Jim has told us he does not want to be contacted by us but, for historical reasons, his contact record cannot be deleted. There are many dozens of places within a number of applications used by a number of different teams that can send an email to a list of people. I need to make sure no email is ever sent to his email address. I can't simply put a flag on the #tblEmails for reasons I won't bore you with. Apart from those reasons, I have been told to create #tblNoEmail and maintain a list of email addresses that must never be contacted. Whenever any email is sent from any of our applications (and there are about 20 teams using their own project planning systems over the same database) - I have to check any email address we are about to send an email to is not in 'tblNoEmail'

Something like ....


SELECT *
FROM #tblContacts AS c
INNER JOIN #tblEmails AS e ON c.ContactID = e.ContactID
WHERE e.EmailID NOT IN (SELECT n.EmailID FROM #tblNoEmail AS n);


should do it (although there is a syntax error in my code above that I can't find and that statement above will not run for some reason ? ). But, given there are 200,000 contact records, about 500,000 records in #tblEmails and will be a few thousand in #tblNoEmail - what's the most efficient way of always making sure that any email in a list of email addresses I am about to send an email to will not contain any of the email addresses in #tblNoEmail