>>>>I am trying to check for duplicates and am not understanding why when I run the first commented out query below I get 2 rows back, yet when I try to join those rows back to their duplicate row I get no rows.
>>>>
>>>>Here's my script:
>>>>
>>>>
>>>>declare @ClientReminders table (
>>>> [clr_pk] [uniqueidentifier] NOT NULL,
>>>> [clr_clifk] [uniqueidentifier] NOT NULL,
>>>> [clr_type] [smallint] NOT NULL,
>>>> [clr_date] [date] NOT NULL,
>>>> [clr_actioned] [date] NULL,
>>>> [clr_empfk] [uniqueidentifier] NULL,
>>>> [clr_covfk] [uniqueidentifier] NULL,
>>>> [clr_pedfk] [uniqueidentifier] NULL,
>>>> [clr_pplfk] [uniqueidentifier] NULL,
>>>> [clr_eidfk] [uniqueidentifier] NULL
>>>>)
>>>>
>>>>insert into @ClientReminders values ('7E8746DF-6B9C-F84C-324A-6862DC00B62A'
>>>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>>>> ,3
>>>> ,'2017-08-29'
>>>> ,NULL
>>>> ,'7FE26FBE-A4D1-6A4E-2C48-674E686BDD42'
>>>> ,NULL
>>>> ,NULL
>>>> ,NULL
>>>> ,'70AE0219-2A17-244D-3949-F93B3BC92954')
>>>>
>>>>insert into @ClientReminders values ('63B2B21F-8B9B-6D40-18C4-8AC6EDD28DA5'
>>>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>>>> ,3
>>>> ,'2017-08-29'
>>>> ,NULL
>>>> ,'7FE26FBE-A4D1-6A4E-2C48-674E686BDD42'
>>>> ,NULL
>>>> ,NULL
>>>> ,NULL
>>>> ,'70AE0219-2A17-244D-3949-F93B3BC92954')
>>>>
>>>>insert into @ClientReminders values ('7A6B3989-C44C-5448-2BD4-BB97E02E9B5E'
>>>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>>>> ,3
>>>> ,'2017-10-02'
>>>> ,NULL
>>>> ,'72F7E803-3921-8D47-37F3-E3EEC599784D'
>>>> ,NULL
>>>> ,NULL
>>>> ,NULL
>>>> ,'B83600FD-3E43-C34C-0F0B-7FD3E3F7D8FE')
>>>>
>>>>insert into @ClientReminders values ('3B374CA2-F806-8141-26DF-F9BA7AD404B5'
>>>> ,'7A2D25C8-3C95-884A-0184-B80AED1F995D'
>>>> ,3
>>>> ,'2017-10-02'
>>>> ,NULL
>>>> ,'72F7E803-3921-8D47-37F3-E3EEC599784D'
>>>> ,NULL
>>>> ,NULL
>>>> ,NULL
>>>> ,'B83600FD-3E43-C34C-0F0B-7FD3E3F7D8FE')
>>>>
>>>>;with cte as (select *, row_number() over
>>>> (partition by
>>>> [clr_clifk]
>>>> ,[clr_type]
>>>> ,[clr_date]
>>>> ,[clr_actioned]
>>>> ,[clr_empfk]
>>>> ,[clr_covfk]
>>>> ,[clr_pedfk]
>>>> ,[clr_pplfk]
>>>> ,[clr_eidfk]
>>>> order by [clr_clifk]
>>>> ,[clr_type]
>>>> ,[clr_date]
>>>> ,[clr_actioned]
>>>> ,[clr_empfk]
>>>> ,[clr_covfk]
>>>> ,[clr_pedfk]
>>>> ,[clr_pplfk]
>>>> ,[clr_eidfk]
>>>>) as Rn
>>>>from @ClientReminders)
>>>>
>>>>/* this gives me 2 rows
>>>>select cte.*
>>>> from cte
>>>>where rn > 1
>>>>*/
>>>>
>>>>
>>>>select keep.clr_pk as KeepId,
>>>> dup.clr_pk as DupId
>>>> ,dup.[clr_clifk]
>>>> ,dup.[clr_type]
>>>> ,dup.[clr_date]
>>>> ,dup.[clr_actioned]
>>>> ,dup.[clr_empfk]
>>>> ,dup.[clr_covfk]
>>>> ,dup.[clr_pedfk]
>>>> ,dup.[clr_pplfk]
>>>> ,dup.[clr_eidfk]
>>>>from cte dup
>>>> inner join (SELECT * from cte
>>>> WHERE Rn = 1) keep on
>>>> dup.[clr_clifk] = keep.clr_clifk
>>>> and dup.[clr_type] = keep.[clr_type]
>>>> and dup.[clr_date] = keep.[clr_date]
>>>> and dup.[clr_actioned] = keep.[clr_actioned]
>>>> and dup.[clr_empfk] = keep.[clr_empfk]
>>>> and dup.[clr_covfk] = keep.[clr_covfk]
>>>> and dup.[clr_pedfk] = keep.[clr_pedfk]
>>>> and dup.[clr_pplfk] = keep.[clr_pplfk]
>>>> and dup.[clr_eidfk] = keep.[clr_eidfk]
>>>>
>>>>delete @clientreminders
>>>>>>>>
>>>>Anybody see what I a cannot see after staring at this too long?
>>>
>>>Because several fields are NULL.
>>>Is SQL Server Null isn't equal to NULL :-)
>>>You should use ISNULL() or COALESCE()
>>
>>Thanks! What do I COALESCE when a uniqueidentifier is the column type?
>
>CAST('' as uniqueidentifier) :-)
DOH! thanks.
Later: I tried select cast('' as uniqueidentifier) and got an error:
Conversion failed when converting from a character string to uniqueidentifier.
