Solved

Coldfusion - Help filtering SQL results to elminate duplicates

Posted on 2015-02-06
11
88 Views
Last Modified: 2015-02-14
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
Comment
Question by:JohnMac328
  • 6
  • 3
  • 2
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40595038
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
 

Author Comment

by:JohnMac328
ID: 40595732
Getting a syntax error that I can't resolve
syntaxerror.jpg
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 40595794
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:JohnMac328
ID: 40598185
Now it is giving this error
error2.jpg
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 350 total points
ID: 40598338
>>       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
 

Assisted Solution

by:JohnMac328
JohnMac328 earned 0 total points
ID: 40598353
Hi agx! - Yes that worked.   How do we do the points?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40598368
@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
 

Author Comment

by:JohnMac328
ID: 40598381
Well I used yours because when I fixed the syntax error the records were still duplicating.  How about 50/50?
0
 

Author Comment

by:JohnMac328
ID: 40598382
Actually 70/30
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40598398
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
 

Author Closing Comment

by:JohnMac328
ID: 40609531
Great job!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question