• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

Coldfusion - Help filtering SQL results to elminate duplicates

I have a voting system where first employees are nominated and then another group of managers vote on the people nominated.  The nominated votes go into a table called nominated.  Each record is displayed with an option box selection and when the person is selected the record then goes into a table called leadervotes.  Those are the nominated people that were voted on.  Here is the display of the query showing the nominated employees

SELECT DISTINCT
                      nominations.recordid, nominations.voterid, employee_data.first_name AS VoterFirstName, employee_data.last_name AS VoterLastName, nominations.mvpid,
                      employee_data_1.first_name AS MVPFIRSTNAME, employee_data_1.last_name AS MVPLASTNAME, nominations.mvpdept, nominations.mvpreason,
                      nominations.voted, employee_data.ID
FROM         nominations INNER JOIN
                      employee_data ON nominations.voterid = employee_data.ID INNER JOIN
                      employee_data AS employee_data_1 ON nominations.mvpid = employee_data_1.ID


Here is the table with the employees that were voted on

SELECT    recordid, leaderid, voteid, votedate, mvp_ast, votelevel, voted, astdept
FROM         leadervotes


Now they want the person in the nominated list to not display when they are voted on - For example manager logs-in - votes on a person and that record no longer shows based on the session id of the manager.

Thats where the problem is.  I add the vote results table and change the query so that it displays the data where <> to session id.
SELECT DISTINCT
                      nominations.recordid, nominations.voterid, employee_data.first_name AS VoterFirstName, employee_data.last_name AS VoterLastName, nominations.mvpid,
                      employee_data_1.first_name AS MVPFIRSTNAME, employee_data_1.last_name AS MVPLASTNAME, nominations.mvpdept, nominations.mvpreason,
                      nominations.voted, employee_data.ID, leadervotes.leaderid, leadervotes.voteid
FROM         nominations INNER JOIN
                      employee_data ON nominations.voterid = employee_data.ID INNER JOIN
                      employee_data AS employee_data_1 ON nominations.mvpid = employee_data_1.ID INNER JOIN
                      leadervotes ON nominations.mvpid = leadervotes.voteid
GROUP BY nominations.recordid, nominations.voterid, employee_data.first_name, employee_data.last_name, nominations.mvpid, employee_data_1.first_name,
                      employee_data_1.last_name, nominations.mvpdept, nominations.mvpreason, nominations.voted, employee_data.ID, leadervotes.leaderid, leadervotes.voteid
HAVING      (leadervotes.leaderid <> #SESSIONID#)



This does filter correctly but it also leaves the list with double records.  Anyway to display the list without double records?
sql.jpg
0
JohnMac328
Asked:
JohnMac328
  • 6
  • 3
  • 2
3 Solutions
 
Anthony PerkinsCommented:
Either use DISTINCT or GROUP BY, using both is pointless.  Also, use aliases.

See if this makes any difference:
SELECT	n.recordid,
        n.voterid,
        e.first_name AS VoterFirstName,
        e.last_name AS VoterLastName,
        n.mvpid,
        e1.first_name AS MVPFIRSTNAME,
        e1.last_name AS MVPLASTNAME,
        n.mvpdept,
        n.mvpreason,
        n.voted,
        e.ID,
        l.leaderid,
        l.voteid
FROM    nominations n
        INNER JOIN employee_data e ON n.voterid = e.ID
        INNER JOIN employee_data e1 employee_data_1 ON n.mvpid = e1.ID
        INNER JOIN leadervotes ON n.mvpid = l.voteid
WHERE	l.leaderid <> #SESSIONID#
GROUP BY n.recordid,
        n.voterid,
        e.first_name,
        e.last_name,
        n.mvpid,
        e1.first_name,
        e1.last_name,
        n.mvpdept,
        n.mvpreason,
        n.voted,
        e.ID,
        l.leaderid,
        l.voteid

Open in new window


If not post the output and show the desired result.
0
 
JohnMac328Author Commented:
Getting a syntax error that I can't resolve
syntaxerror.jpg
0
 
Anthony PerkinsCommented:
You are correct.  It should be:
SELECT	n.recordid,
        n.voterid,
        e.first_name AS VoterFirstName,
        e.last_name AS VoterLastName,
        n.mvpid,
        e1.first_name AS MVPFIRSTNAME,
        e1.last_name AS MVPLASTNAME,
        n.mvpdept,
        n.mvpreason,
        n.voted,
        e.ID,
        l.leaderid,
        l.voteid
FROM    nominations n
        INNER JOIN employee_data e ON n.voterid = e.ID
        INNER JOIN employee_data e1 ON n.mvpid = e1.ID
        INNER JOIN leadervotes ON n.mvpid = l.voteid
WHERE	l.leaderid <> #SESSIONID#
GROUP BY n.recordid,
        n.voterid,
        e.first_name,
        e.last_name,
        n.mvpid,
        e1.first_name,
        e1.last_name,
        n.mvpdept,
        n.mvpreason,
        n.voted,
        e.ID,
        l.leaderid,
        l.voteid

Open in new window


You should also wean yourself from using the designers, they are flawed at best and do not always provide relevant error messages.   Use the query window instead.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
JohnMac328Author Commented:
Now it is giving this error
error2.jpg
0
 
_agx_Commented:
>>       INNER JOIN leadervotes ON n.mvpid = l.voteid

Regarding the error, the code is missing the alias "l" for the leader table. It should be this:

                INNER JOIN leadervotes l ON n.mvpid = l.voteid

BUT .. if I'm reading it right, a NOT EXISTS might be more appropriate. If you don't actually need to return any of the leader table columns, you don't need it in the JOIN. Then duplicates are a non-issue. Try adding something like this to your original JOIN.  The relationships are based on your 2nd JOIN.

SELECT DISTINCT
       n.recordid
       , n.voterid
       , vtr.first_name AS VoterFirstName
       , vtr.last_name AS VoterLastName
       , n.mvpid
       , mvpfirst_name AS MVPFIRSTNAME
       , mvp.last_name AS MVPLASTNAME
       , n.mvpdept
       , n.mvpreason
       , n.voted, vtr.ID
FROM   nominations n 
          INNER JOIN employee_data vtr ON n.voterid = vtr.ID 
          INNER JOIN employee_data mvp ON n.mvpid = mvp.ID
WHERE NOT EXISTS 
(
     <!--- change sql type for #SESSIONID# as needed --->
     SELECT 1
     FROM   leaderVotes lv
     WHERE  lv.voteID = n.mvpid 
     AND     lv.leaderID = <cfqueryparam value="#SESSIONID#" cfsqltype="cf_sql_integer">

)

Open in new window

0
 
JohnMac328Author Commented:
Hi agx! - Yes that worked.   How do we do the points?
0
 
_agx_Commented:
@JohnMac328 - However you want to allocate it is cool w/me.  But if you used Anthony Perkins's answer, then the bulk/all should go to him, since I just pointed out a typo :)
0
 
JohnMac328Author Commented:
Well I used yours because when I fixed the syntax error the records were still duplicating.  How about 50/50?
0
 
JohnMac328Author Commented:
Actually 70/30
0
 
_agx_Commented:
However you want to do it is fine, because all of the responses contain some useful info IMO (aliasing, group vs distinct, query editors, not exists vs join) :)
0
 
JohnMac328Author Commented:
Great job!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now