Click here to Skip to main content
15,939,853 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table
ProcessID	ID	    DupId
4	        5555	 4444
3	        4444	 3333
2	        3333	 2222
1	        2222	  0
44	        1212	 1111
33	        1111	 1010
22	        1010	  0

If i pass parameter as ProsessID = 4 i need output like this:

ID
5555
4444
3333

If i pass parameter as ProsessID = 44 i need output like this:

ID
1212
1111
1010

Please help
Posted
Comments
OriginalGriff 19-Jan-16 3:59am    
And?
What have you tried?
Where are you stuck?
What help are you looking for?
[no name] 19-Jan-16 4:03am    
I tried this :

select Id from TableA where ProcessID = 4
Union all
select DupId from TableA where ProcessID = 4
Union all
select DupId from TableA where Id in (select DupId from TableA where ProcessID = 4)

But i want to do it in 1 quenry using joins i dont know how to do it :(
and i already mentioned in my question what output i need
Tomas Takac 19-Jan-16 5:46am    
I guess you should post this as an solution.
Tomas Takac 19-Jan-16 4:18am    
Why for processId=4 2222 is not included?
[no name] 19-Jan-16 4:20am    
Because i need only 2 levels

If it is just a single hierarchy find you can use it as below,
MSSQL
<pre lang="C#">
Declare @i int = 44 -- This will be your process Id
Select P1.Id, P1.DupId, P2.DupId from ProcessTable P1
left join ProcessTable P2 on P1.DupId = P2.Id
where P1.ProcessId = @i
But if your hierarchy is more than 1 then you can use Common Table expresssion.
Also your result will come in single row and not mutiple rows.</pre>
 
Share this answer
 
I think this is what you are looking

DECLARE @ProcessId AS int = 44;
with Duplicates(ID) as
(
SELECT distinct ID FROM ProcessWithDuplicates where ProcessID = @ProcessId
UNION ALL
SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId
UNION ALL
(
    SELECT DupId FROM ProcessWithDuplicates where ID IN (SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId)
        or  DupId in (SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId)
)
)
select distinct ID from Duplicates;


Like

SELECT DupId FROM ProcessWithDuplicates where ID IN	(SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId) 
			or  DupId in (SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId)


You need.Hope this will help.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900