CODEWITHSUNDEEP
sql-server
Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

Get All Duplicate Data by Parent or Child ID [SQL Server]

I have table with Named EmailChangeRequest which has following columns:

1. ChangeRequestId -- (INT) Primary Key + Auto Increment
2. UserId -- (INT)
3. VerificationCode -- (VARCHAR)
4. IsVerified -- (BIT)
5. OriginalRequestId -- (INT) -- Foreign Key with ChangeRequestId

Here is EmailChangeRequest table Data

ChangeRequestId  |  UserId  |  VerificationCode  | IsVerified | OriginalRequestId
    22           |   1      |    896524          |     0      |        NULL
    23           |   1      |    968521          |     0      |        22
    24           |   1      |    156874          |     0      |        22
    25           |   1      |    658932          |     0      |        22
    26           |   2      |    250489          |     0      |        NULL

Now, What I can achieve: If I Pass ChangeRequestId = 22 then he gives me all the rows whose ChangeRequestId or OriginalRequestId = 22. I can easily achieve that with the following query.

SELECT * FROM EmailChangeRequest
WHERE ChangeRequestId = 22 OR OriginalRequestId = 22

What I want: If I Pass ChangeRequestId as 23 or 24 or 25, It also give me the same result as it gives for ChangeRequestId = 22.

Any Idea, How I achieve this?

Upvotes: 0

Views: 94

Answers (5)

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

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 @ChangeRequestId INT = 23
DECLARE @OriginalRequestId INT;

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

SELECT * FROM EmailChangeRequest
WHERE ChangeRequestId = @OriginalRequestId
  OR OriginalRequestId = @OriginalRequestId

Upvotes: 0

Sujeet Sinha
Sujeet Sinha

Reputation: 2423

You can use the following script:

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

IF @desiredID = 0
BEGIN 
   SELECT * FROM EmailChangeRequest
   WHERE ChangeRequestId = @desiredID OR OriginalRequestId = @desiredID
END
ELSE
BEGIN
   SELECT * FROM EmailChangeRequest
   WHERE ChangeRequestId = 23 OR OriginalRequestId = 23 --This is for all other cases
END

Upvotes: 0

Joe C
Joe C

Reputation: 3993

Is this what you are looking for?

Drop Table #EmailChangeRequest
Create Table #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 @ID Int = 22
;With cteReferenced As
(
Select OriginalRequestId 
    From #EmailChangeRequest 
    Where ChangeRequestId = @ID Or OriginalRequestId = @ID
)
SELECT * 
    FROM #EmailChangeRequest
    WHERE ChangeRequestId = @ID
Union -- removes duplicates between two queries ie @ID = 22 as opposed to Union All
SELECT * 
    FROM #EmailChangeRequest
    WHERE OriginalRequestId In (Select OriginalRequestId From cteReferenced) 
        Or ChangeRequestId In (Select OriginalRequestId From cteReferenced)

Upvotes: 1

Eric
Eric

Reputation: 703

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') is not null
    drop table #ecr;

create table #ecr ( changerequestid   int not null
,                   userid            int not null
,                   verficationcode   varchar(10) not null
,                   isverified        bit
,                   originalrequestid int null );

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 example

with 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 changerequestid
as
(
    select *
    from #ecr as a
    where changerequestid = @req_id
    union all
    select a.*
    from       #ecr as a
    inner join 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
union
select a.*
from       #ecr a
inner join req  r on a.originalrequestid = r.changerequestid

Upvotes: 1

Whencesoever
Whencesoever

Reputation: 2306

For example for 23:

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

Brackets makes job.

Upvotes: 0

Related Questions