How to delete duplicates when using innerjoin?

Question

Hey Guys!

I Using this code

SELECT

[TB].[Unitid],

[SO].[DServiceOrderID],

[SO].[ServiceOrderNumberCleaned],

[SO].[BasicStart],

[SO].[FunctionalLocation],

[SO].[MaintActivityType],

[SO].[MaintActivityTypeText],

[SO].[Ordertype],

[SO].[ServiceOrderLongTextCleaned],

[SO].[ServiceOrderOperationTextCleaned],

[SO].[ActualStart],

[CDA].[Employee]

FROM [dim].[ServiceOrder] AS SO

INNER JOIN [dim].[Turbine] AS TB ON Try_Cast(SO.[Room] As int) = TB.[Unitid]

INNER JOIN [pa].[SAPTimeRegistration] AS CDA on CDA.[ServiceOrderNumberTrim]= SO.ServiceOrderNumberCleaned

WHERE

[TB].[SBU] = 'LAT'

and [SO].[ActualStart] between '2023-01-01' and GETDATE()

It shows me duplicated values... How can I fix that?

Answer 1

For your duplicate result, one common reason for this is when there are multiple matches between the tables being joined, resulting in duplicate rows in your output.

To address this issue, you can try using the DISTINCT keyword in your SELECT statement. Try the below and see... I just rewrite your statement for you. let's see if that works. This way, you're only returning unique rows.

SELECT DISTINCT

[TB].[Unitid],

[SO].[DServiceOrderID],

[SO].[ServiceOrderNumberCleaned],

[SO].[BasicStart],

[SO].[FunctionalLocation],

[SO].[MaintActivityType],

[SO].[MaintActivityTypeText],

[SO].[Ordertype],

[SO].[ServiceOrderLongTextCleaned],

[SO].[ServiceOrderOperationTextCleaned],

[SO].[ActualStart],

[CDA].[Employee]

FROM

[dim].[ServiceOrder] AS SO

INNER JOIN

[dim].[Turbine] AS TB ON TRY_CAST(SO.[Room] AS INT) = TB.[Unitid]

INNER JOIN

[pa].[SAPTimeRegistration] AS CDA ON CDA.[ServiceOrderNumberTrim] = SO.ServiceOrderNumberCleaned

WHERE

[TB].[SBU] = 'LAT'

AND [SO].[ActualStart] BETWEEN '2023-01-01' AND GETDATE();

Answer 2

It shows me duplicated values... How can I fix that?

Then your JOIN conditions are wrong; or your table design.

But we don't know both, so difficult to say.

Please post table design as DDL, some sample data as DML statement and the expected result.