I am using a select DISTINCT to capture emails of individuals who wants to be notified if a system goes down. It is my understanding that when using the DISTINCT it will give me just one record even if multiple records are found. For example, if system 3000 and system 3100 is down...when the page that sends the emails is executed it should send just one email to each person. If a person has requested to be notified when these systems go down, they should receive just one email, not two.
In my query, the system user table contains the employee ID and the system ID of those systems the employee wants to be notified. For example:
Now, if system 3000 and 3100 goes down then only 4 emails should be generated:
for as1122, jr2342, ll4455, and al0729.
mk3242 wouldn't get one because they don't have that system ID in the table and ll4455 and al0729 would only get 1 email even though they are signed up for both 3000 and 3100.
<!---- for illustrative purposes ------>
<cfset PrimarySystem = 3000>
<cfset SecondarySystem = 3100>
<cfset EmailSystemList = ListAppend(PrimarySystem, SecondarySystem)>
<!----- the query to get the emails,
the query pulls from two tables...a user table and a user system table... ----->
<cfquery datasource="mydns" name="getEmailList">
SELECT DISTINCT UP.strEmail, SUP.intSystemID
FROM tblUserProf UP
INNER JOIN tblUserSysProf SUP ON UP.empID = SUP.empID
WHERE (UP.bitActive = 1)
AND (SUP.intSystemId IN (#EmailSystemList#))