Get All Duplicate Data By Parent Or Child Id [sql Server]

September 30, 2023 Post a Comment

I have table with Named EmailChangeRequest which has following columns: 1. ChangeRequestId -- (INT) Primary Key + Auto Increment 2. UserId -- (INT) 3. VerificationCode -- (VARCHAR)

Solution 1:

Use a recursive CTE - here is an example using your table and data: -- check for temp table existence and drop if necessary

if object_id('tempdb..#ecr') isnotnulldroptable #ecr;

createtable #ecr ( changerequestid   intnotnull
,                   userid            intnotnull
,                   verficationcode   varchar(10) notnull
,                   isverified        bit
,                   originalrequestid intnull );

insert #ecr
values ( 22, 1, 896524, 0, null )
,      ( 23, 1, 968521, 0, 22   )
,      ( 24, 1, 156874, 0, 22   )
,      ( 25, 1, 658932, 0, 22   )
,      ( 26, 1, 250489, 0, null );

declare@req_id int=23; -- your examplewith req -- recursive CTE - anchor is the row with the request id you submitted.-- recursive member is the row with the first row's originalrequestid as it's own changerequestidas
(
    select*from #ecr as a
    where changerequestid =@req_id
    unionallselect a.*from       #ecr as a
    innerjoin req  as r on (a.changerequestid = r.originalrequestid)
)
-- the above will give us the change request # 23 and all of its parent rows -- from the originalchangerequest column, until there are no more parent rows -- (until originalchangerequestid is null)-- we use the recursive CTE, and then do ANOTHER union, select all rows which -- have any of the originalrequestid equal to any of the changerequestid from our CTE-- this will give us the request we asked for, it's parent row, and all other rows with -- any of those rows as their parents.select r.*from req r
unionselect a.*from       #ecr a
innerjoin req  r on a.originalrequestid = r.changerequestid

Solution 2:

Is this what you are looking for?

DropTable #EmailChangeRequest
CreateTable #EmailChangeRequest (ChangeRequestId Int,UserId Int,VerificationCode Int, IsVerified Int, OriginalRequestId Int)

Insert #EmailChangeRequest Values (22, 1,896524, 0,NULL)
Insert #EmailChangeRequest Values (23, 1,968521, 0,22)
Insert #EmailChangeRequest Values (24, 1,156874, 0,22)
Insert #EmailChangeRequest Values (25, 1,658932, 0,22)
Insert #EmailChangeRequest Values (26, 2,250489, 0,NULL )
Insert #EmailChangeRequest Values (22, 1,896524, 0,NULL)
Insert #EmailChangeRequest Values (23, 1,968521, 0,22)
Insert #EmailChangeRequest Values (24, 1,156874, 0,22)
Insert #EmailChangeRequest Values (25, 1,658932, 0,22)
Insert #EmailChangeRequest Values (26, 2,250489, 0,NULL )
Insert #EmailChangeRequest Values (27, 1,968521, 0,28)
Insert #EmailChangeRequest Values (28, 1,156874, 0,NULL)
Insert #EmailChangeRequest Values (29, 1,658932, 0,28)  


Select*From #EmailChangeRequest

Declare@IDInt=22
;With cteReferenced As
(
Select OriginalRequestId 
    From #EmailChangeRequest 
    Where ChangeRequestId =@IDOr OriginalRequestId =@ID
)
SELECT*FROM #EmailChangeRequest
    WHERE ChangeRequestId =@IDUnion-- removes duplicates between two queries ie @ID = 22 as opposed to Union AllSELECT*FROM #EmailChangeRequest
    WHERE OriginalRequestId In (Select OriginalRequestId From cteReferenced) 
        Or ChangeRequestId In (Select OriginalRequestId From cteReferenced)

Solution 3:

For example for 23:

SELECT*FROM EmailChangeRequest
WHERE (ChangeRequestId =22OR OriginalRequestId =22) OR (ChangeRequestId =23OR OriginalRequestId =23)

Brackets makes job.

Solution 4:

You can use the following script:

DECLARE@desiredIDINT=0SELECT@desiredID= OriginalRequestId  
FROM EmailChangeRequest
WHERE ChangeRequestId =23-- Whatever value you pass

IF @desiredID=0BEGINSELECT*FROM EmailChangeRequest
   WHERE ChangeRequestId =@desiredIDOR OriginalRequestId =@desiredIDENDELSEBEGINSELECT*FROM EmailChangeRequest
   WHERE ChangeRequestId =23OR OriginalRequestId =23--This is for all other casesEND

Solution 5:

I check all the 4 answers posted here. Two of them does not work and another two looks complicated to me. At the end, I came up with the below solution:

DECLARE@ChangeRequestIdINT=23DECLARE@OriginalRequestIdINT;

SELECT@OriginalRequestId= ISNULL(OriginalRequestId, @ChangeRequestId)
FROM EmailChangeRequest
WHERE ChangeRequestId =@ChangeRequestIdSELECT*FROM EmailChangeRequest
WHERE ChangeRequestId =@OriginalRequestIdOR OriginalRequestId =@OriginalRequestId

Lab Sql