CODEWITHSUNDEEP
sql-server
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Redundant Rows from Case Statement and Join SQL Server

I am getting some duplicates with my case statement.

How to avoid this without using subqueries?

SELECT  

  [S].[TicketNbr] AS 'Ticket Nbr'
, [S].[status_description] AS 'Status'
, ( CASE  WHEN [A].OldValue_Text = 'Service Desk' THEN 1  ELSE 0  END) AS 'Moved from Old Board?' END) AS 'Moved from Old Board?'

FROM [Service] AS [S]
LEFT OUTER JOIN [Audit] AS [A]   
ON [A].[TicketNbr]=[S].[TicketNbr]

GROUP BY [S].[TicketNbr]
, [S].[status_description]
,(CASE  WHEN [A].OldValue_Text = 'Service Desk' THEN 1  ELSE 0  END)

Note I only want the row with 1.

enter image description here

Upvotes: 0

Views: 162

Answers (2)

lc.
lc.

Reputation: 116498

Those are not duplicates, they're separate records resulting from multiple rows in Audit - in your example one with OldValue_Text of 'Service Desk' and one which is not.

Now, if you're only looking for the 1 when present and 0 otherwise, you could do one of three things:

  1. Keep your existing query and take the max of your third column:

    SELECT      
  [S].[TicketNbr] AS [Ticket Nbr],
  [S].[status_description] AS [Status],
  MAX((CASE WHEN [A].OldValue_Text = 'Service Desk' THEN 1 ELSE 0  END)) AS [Moved from Old Board?]

FROM [Service] AS [S]
LEFT OUTER JOIN [Audit] AS [A]   
  ON [A].[TicketNbr]=[S].[TicketNbr]

GROUP BY [S].[TicketNbr],
  [S].[status_description]

  2. Use EXISTS with a correlated subquery (I know you say you don't want to use one, but this is a valid solution):

    SELECT      
  [S].[TicketNbr] AS [Ticket Nbr],
  [S].[status_description] AS [Status],
  CASE WHEN EXISTS (SELECT 1 FROM [Audit] AS [A]   
    WHERE [A].[TicketNbr]=[S].[TicketNbr]
    AND [A].OldValue_Text = 'Service Desk') THEN 1 ELSE 0 END AS [Moved from Old Board?]

FROM [Service] AS [S]

  3. Add the criteria OldValue_Text = 'Service Desk' to the join clause instead. This will reduce the joined rows to the ones you are after. (Note this assumes there is only one row that matches; if not you may have to add DISTINCT):

    SELECT      
  [S].[TicketNbr] AS [Ticket Nbr],
  [S].[status_description] AS [Status],
  CASE WHEN [A].[TicketNbr] IS NOT NULL THEN 1 ELSE 0 END AS [Moved from Old Board?]

FROM [Service] AS [S]
LEFT OUTER JOIN [Audit] AS [A]   
  ON [A].[TicketNbr]=[S].[TicketNbr]
  AND [A].OldValue_Text = 'Service Desk'

Upvotes: 1

MBurnham
MBurnham

Reputation: 381

Your case statement is in the select clause, so it isn't giving you duplicates. Also these aren't duplicates, they are completely different records. I believe all you will need to do is add a where clause that only pulls records with OldValue_Text of 'Service Desk'

WHERE [A].OldValue_Text LIKE 'Service Desk'

You would add this after your from clause but before your group by clause.

This will reduce your result set to only the items that have a value of 1 for Moved from old Board?

If this gives you duplicates, then you can just add a distinct to your select clause

SELECT  DISTINCT

      [S].[TicketNbr] AS 'Ticket Nbr'
    , [S].[status_description] AS 'Status'
    , ( CASE  WHEN [A].OldValue_Text = 'Service Desk' THEN 1  ELSE 0  END) AS 'Moved from Old Board?' END) AS 'Moved from Old Board?'

Upvotes: 0

Related Questions