Finding the tree structure

Transact-SQL
1

I have the following table:

SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '2' as Version, 1 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '1.5' as Version, 0 as Counter, '05-03-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2.5' as Version, 1 as Counter, '05-04-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '3' as Version, 2 as Counter, '05-05-2011' as CreatedDate UNION ALL
SELECT 'C' as Item, '2' as Version, 3 as Counter, '06-01-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.3' as Version, 0 as Counter, '01-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '1.2' as Version, 1 as Counter, '03-10-2011' as CreatedDate UNION ALL
SELECT 'D' as Item, '3' as Version, 2 as Counter, '04-10-2011' as CreatedDate
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output

http://imgur.com/a2SxnAV

Can someone please help me with this?

Thank you in advance

2

Your gave an example but the output is not shown here, so it is difficult to know what your want.

3

Please try with the below query and revert if it is not fulfill your requirement
select *
from #table a
left outer join #table b
on a.Item = b.Item
and a.version< b.version
where a.version=1.2

4

Thank you for your efforts mate..thats the start I wanted