[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Coldfusion - Help filtering SQL results to elminate duplicates

Posted on 2015-02-06
11
Medium Priority
?
95 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 600 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

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

Accepted Solution

by:
_agx_ earned 1400 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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