Solved

Coldfusion - Help filtering SQL results to elminate duplicates

Posted on 2015-02-06
11
79 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:JohnMac328
Comment Utility
Now it is giving this error
error2.jpg
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 350 total points
Comment Utility
>>       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
Backup Your Microsoft Windows Server®

Backup 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.

 

Assisted Solution

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

Expert Comment

by:_agx_
Comment Utility
@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
Comment Utility
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
Comment Utility
Actually 70/30
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
Comment Utility
Great job!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now