[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

SQL DISTINCT not functioning correctly in CFQUERY

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:
TBLSYSUSERPROF
EmpID                  SystemID
as1122                   3000
mk3242                   4000
jr2342                      3100
ll4455                       3000
ll4455                       3100
al0729                      3000
al0729                      3100

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#)) 
</cfquery>

Open in new window

0
Lee R Liddick Jr
Asked:
Lee R Liddick Jr
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
I am not sure whether I understand your issue

If you mean - you want to get the emailid distinctly, try this modified query (removed systemid)
<cfquery datasource="mydns" name="getEmailList">
SELECT 	DISTINCT UP.strEmail
FROM        tblUserProf UP 
          INNER JOIN tblUserSysProf SUP ON UP.empID = SUP.empID 
WHERE    (UP.bitActive = 1) 
AND 	(SUP.intSystemId IN (#EmailSystemList#)) 
</cfquery>

Open in new window

0
 
Anthony PerkinsCommented:
Something like this perhaps:

SELECT       UP.strEmail, MAX(SUP.intSystemID)   -- Just return one
FROM        tblUserProf UP
                 INNER JOIN tblUserSysProf SUP ON UP.empID = SUP.empID
WHERE    UP.bitActive = 1
               AND SUP.intSystemId IN (#EmailSystemList#)
GROUP BY
                UP.strEmail
0
 
gdemariaCommented:
Select Distinct works exactly as you have said, it returns only one record by merging all duplicated records.

You have selected two fields, you will never see those two fields duplicated in your output.  That is what select distinct does.   The problem is that you really only wanted to make the EMAIL distinct.   So, you only want to select the email.   By selecting the email AND the system, you are allowing the email to be repeated for each down system.


What you want to do is only select the email..


SELECT       DISTINCT UP.strEmail
FROM        tblUserProf UP
          INNER JOIN tblUserSysProf SUP ON UP.empID = SUP.empID
WHERE    (UP.bitActive = 1)
AND       (SUP.intSystemId IN (#EmailSystemList#))
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Aaron TomoskyTechnology ConsultantCommented:
Why so complicated? Can't it just be this simple?

Select distinct empid from TBLSYSUSERPROF
Where systemid in ('csv list of systems down')
0
 
OP_ZaharinCommented:
hi leerljr68,
- is there any reason why do you need to get both emailid and systemid on your original select distinct?

- if you need to get SUP.intSystemID, i guess you have to have 2 sets of query. 1 is for distinct the email as per RajkumarGS posting and another query is for getting the SystemID.
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
It was the other selects that was causing my issue. Ugh. I knew it was something I was just overlooking.
0
 
Anthony PerkinsCommented:
Perhaps because the recipient would like to know what system went down, rather than be told that a system went down.

:)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now