Duplicate record in SQL
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.ReferenceN
INI.EngDir,INI.SecCode,SER
INI.EngSec,dbo.CurrentUser
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=T
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.
--------------------------
SELECT
TASK.ID as CurrentTaskId, TRK.Details,TRK.ReferenceN
INI.EngDir,INI.SecCode,SER
INI.EngSec,dbo.CurrentUser
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=T
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.
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.ReferenceN
INI.EngDir,INI.SecCode,SER
INI.EngSec,dbo.CurrentUser
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=T
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.
SELECT DISTINCT
TASK.ID as CurrentTaskId, TRK.Details,TRK.ReferenceN
INI.EngDir,INI.SecCode,SER
INI.EngSec,dbo.CurrentUser
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=T
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.
by the way the DISTINCT keyword elsiminates only the records that are having the same value in all columns...
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
you may want to read this article to understand the issue, and solve it:
https://www.experts-exchange.com/A_3203.html
https://www.experts-exchange.com/A_3203.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Kept outer join should be left outer join.
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.