T-SQL
Could you pls. advice me on following dilemma
I am having table called ‘UNITCONVERTION’, its containing following records.
IDfromFromValuetoValueIDto
-
2100013
3100014
4100018
331000138
10321205
561000133
3810145
205101506
4581103
IDfrom and IDto values are I am getting from another table called ‘UNITNAMES’
Example with first row
ID 2 is ‘Gram’
ID 3 is ‘KiloGram’ means I am using unitconvertion 1000 gram = 1 Kilogram
With above scenario, if i pass parameter within IDfrom or IDto, I need all the corresponding values and rows
For example (if parameter value is38 I need rows like following order)
IDfromFromValuetoValueIDto
-
561000133
331000138
3810145
4581103
10321205
205101506
Anyone please help me with T-SQL scripting
From what I understand, it seems like you have a recursive relationship here. If so, you can use the recursive CTEs feature. The solution for your problem will look like:
declare @id int;
set @id = 38;
with ascendants
as (
select u.IDfrom, u.FromValue, u.toValue, u.IDto
from UNITCONVERTION as u
where u.IDto = @id
union all
select u.IDfrom, u.FromValue, u.toValue, u.IDto
from UNITCONVERTION as u
join ascendants as a
on u.IDto = a.IDFrom
),
descendants
as (
select u.IDfrom, u.FromValue, u.toValue, u.IDto
from UNITCONVERTION as u
where u.IDfrom = @id
union all
select u.IDfrom, u.FromValue, u.toValue, u.IDto
from UNITCONVERTION as u
join descendants as d
on u.IDfrom = d.IDto
)
select a.IDfrom, a.FromValue, a.toValue, a.IDto
from ascendants as a
union all
select d.IDfrom, d.FromValue, d.toValue, d.IDto
from descendants as d;The query should be self-explanatory. You can also take a look at the recursive CTEs topic in Books Online. The CTE expressions get ascendants and descendants resp and then we combine the two at the end.
