[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Duplicate record in SQL

Posted on 2011-10-05
9
Medium Priority
?
382 Views
Last Modified: 2012-05-12
Hi experts, i have a very long SQL statement, and this sql return a record, but i found that some of these record are duplicated, is there any way to retrieve proper records without any duplicate records.
---------------------------------------------
SELECT  
TASK.ID as CurrentTaskId, TRK.Details,TRK.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, TRK.InitiatorName AS InitiatorUID,INI.DirCode,
INI.EngDir,INI.SecCode,SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode,
INI.EngSec,dbo.CurrentUserName(TRK.InitiatorName) AS InitiatorName, TRK.RequestedDate AS RequestedDateString, CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate,TRK.Status, TRK.WorkflowPID, TRK.CurrentActorUID, SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode, dbo.CurrentUserName(TRK.CurrentActorUID) AS CurrentActorName, TRK.ApprovedDate,TRK.DeliveredDate, 'Out' as InOut,dbo.ActualKPI(TRK.ApprovedDate,TRK.DeliveredDate) as ActualKPI
FROM
tbl_ServiceTracking AS TRK
INNER JOIN
tblService AS SER ON SER.id = TRK.ServiceNo
INNER JOIN
PR1.dbo.GetUserDetailsE AS INI ON TRK.InitiatorName = INI.PERUserName  
LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE
CAST(TRK.RequestedDate AS DATETIME)>='5/9/2011' AND CAST(TRK.RequestedDate AS DATETIME)<='10/10/2011'
ORDER BY
ReferenceNo DESC
-----------------------------------------------

help me solve this.
0
Comment
Question by:AZZA-KHAMEES
9 Comments
 
LVL 7

Expert Comment

by:luani
ID: 36915913
Hello,
If the original table has them only once than somewhere your joins are duplicating the records (maybe the functions)
If they are twice in the original table than your problem is not the query but the table.

0
 
LVL 26

Expert Comment

by:tigin44
ID: 36916023
simply you can get the distinct records during the select query by using the DISTINCT keyword...

SELECT  DISTINCT
TASK.ID as CurrentTaskId, TRK.Details,TRK.ReferenceNo, SER.ServiceNo + '-' + SER.ServiceName AS ServiceName, SER.KPI, TRK.InitiatorName AS InitiatorUID,INI.DirCode,
INI.EngDir,INI.SecCode,SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode,
INI.EngSec,dbo.CurrentUserName(TRK.InitiatorName) AS InitiatorName, TRK.RequestedDate AS RequestedDateString, CAST(TRK.RequestedDate AS DATETIME) AS RequestedDate,TRK.Status, TRK.WorkflowPID, TRK.CurrentActorUID, SER.DirCode as ServiceDirCode,SER.SecCode as ServiceSecCode, dbo.CurrentUserName(TRK.CurrentActorUID) AS CurrentActorName, TRK.ApprovedDate,TRK.DeliveredDate, 'Out' as InOut,dbo.ActualKPI(TRK.ApprovedDate,TRK.DeliveredDate) as ActualKPI
FROM
tbl_ServiceTracking AS TRK
INNER JOIN
tblService AS SER ON SER.id = TRK.ServiceNo
INNER JOIN
PR1.dbo.GetUserDetailsE AS INI ON TRK.InitiatorName = INI.PERUserName  
LEFT OUTER JOIN
AdobeLCES2.dbo.tb_task AS TASK ON TASK.process_instance_id=TRK.ReferenceNo AND TASK.status=3
WHERE
CAST(TRK.RequestedDate AS DATETIME)>='5/9/2011' AND CAST(TRK.RequestedDate AS DATETIME)<='10/10/2011'
ORDER BY
ReferenceNo DESC


but you should analyze your query for the source of the duplicate records and produce the solution to eliminate them.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 36916031
by the way the DISTINCT keyword elsiminates only the records that are having the same value in all columns...
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.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36916459
you may want to read this article to understand the issue, and solve it:
http://www.experts-exchange.com/A_3203.html
0
 
LVL 3

Assisted Solution

by:jvejskrab
jvejskrab earned 664 total points
ID: 36916898

You probably have some logical mistake in the query or you have some duplicates in the underlying tables
Try rewrite the query instead of using DISTINCT
Start the query with single table and add tables step by step, examining the result for duplicates
0
 

Author Comment

by:AZZA-KHAMEES
ID: 36922451
Thank you all for your replies, i tried DISTINCT and its not working, and yes there are a logical mistake in my query and i am trying to find it, and i will get back with the result
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 668 total points
ID: 36923885
>>i tried DISTINCT and its not working<<
Actually it works fine, it just does not do what you need.  As previously indicated all values in all columns must be the same for it to be considered a duplicate.  Your "duplicates" are just a few columns.  Try excluding all the other columns and DISTINCT "will work" just fine.  On the other hand, usually when someone uses DISTINCT it is usually a sign of desperation at an ill-conceived design...
0
 
LVL 2

Assisted Solution

by:DaemonBarber
DaemonBarber earned 668 total points
ID: 36937116
Try removing a join at a time.  For instance, remove the kept outer join on tb_task.  In all likelyhood, the outer join using I'd/status is returning multiple rows.

Naturally, without knowing your data model this is a guess.  But if you remove the join and it returns distinct records, then you can try and figure out what else to joi. O. To restrict the set.

0
 
LVL 2

Expert Comment

by:DaemonBarber
ID: 36937128
Kept outer join should be left outer join.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
Suggested Courses

829 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