Could you pls. advice me on following dilemma< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
I am having table called ‘UNITCONVERTION’, its containing following records.
IDfrom FromValue toValue IDto
-------------------------------------------------------------------------------------------------------------
2 1000 1 3
3 1000 1 4
4 1000 1 8
33 1000 1 38
103 2 1 205
56 1000 1 33
38 10 1 45
205 10 1 506
45 8 1 103
------------------------------------------------------------------------------------------------------------
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 is 38 I need rows like following order)
IDfrom FromValue toValue IDto
-------------------------------------------------------------------------------------------------------------
56 1000 1 33
33 1000 1 38
38 10 1 45
45 8 1 103
103 2 1 205
205 10 1 506
------------------------------------------------------------------------------------------------------------
Anyone please help me with T-SQL scripting
T-SQL
msn
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.