Solved

Coldfusion - Help filtering SQL results to elminate duplicates

Posted on 2015-02-06
11
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 61
Format Output of Select Statement 2 37
Begin Transaction 12 25
SQL query 45 38
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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