In SQL how to get parent related value by column value

I work on SQL Server 2005 and lowest.

I have a SQL Server t开发者_运维百科able structure like below:

ID    Name     ParentID
-----------------------
1     Root       NULL
2     Business   1
3     Finance    1
4     Stock      3

I want to write a query, when user give input ID=1 then show this output here:

ID    Name     ParentName
-------------------------
1     Root      -
2     Business  Root
3     Finance   Root
4     Stock     Finance    

When user gives input ID=3 then show this output here:

ID    Name     ParentName
-------------------------
3     Finance   Root
1     Root      -
4     Stock     Finance 

When user give input ID=4 then show this output:

ID    Name     ParentName
-------------------------
4     Stock     Finance    
3     Finance   Root
1     Root      -

Thanks in advance. If have any query plz ask. thanks for all

---

SELECT t1.ID, t1.Name, t2.Name AS ParentName FROM tableName t1
LEFT JOIN tableName t2 on t1.ID = t2.ParentID

Replace tableName obviously with your table. Add t2.ID to select list to view match up if you like.

---

Here's modification to marc_s' answer:

  Declare @data table
(ID bigint identity(1,1)   ,Name varchar(100),    ParentID bigint)

Insert into @data SELECT 'Root',NULL
Insert into @data SELECT 'Business',1
Insert into @data SELECT 'Finance',1
Insert into @data SELECT 'Stock',3


DECLARE @StartID INT 
SET @StartID = 3

;WITH DownHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM @data
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM @data d
    INNER JOIN DownHierarchy h ON d.ParentID = h.ID
),
UpHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM @data
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM @data d
    INNER JOIN UpHierarchy h ON d.ID = h.ParentID
)
SELECT *
FROM DownHierarchy
UNION 
SELECT *
FROM UpHierarchy

---

These two recursive CTE's (Common Table Expression) will select the hierarchy from a given node on down in your tree, and also from that node up the tree back to the root. Since it's a CTE, it will work in SQL Server 2005 and newer - but not in SQL Server 2000, unfortunately.

DECLARE @StartID INT 
SET @StartID = 1

;WITH DownHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM dbo.YourTable
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM dbo.YourTable d
    INNER JOIN DownHierarchy h ON d.ParentID = h.ID
),
UpHierarchy AS
(
    SELECT ID, Name, ParentID
    FROM dbo.YourTable
    WHERE ID = @StartID

    UNION ALL

    SELECT d.ID, d.Name, d.ParentID
    FROM dbo.YourTable d
    INNER JOIN UpHierarchy h ON d.ID = h.ParentID
)
SELECT *
FROM DownHierarchy
UNION 
SELECT *
FROM UpHierarchy

Setting @StartID = 1 will give you this output:

ID  Name    ParentID
 1  Root      NULL
 2  Business  1
 3  Finance   1
 4  Stock     3

Setting @StartID = 3 will give you this output:

ID  Name     ParentID
 1  Root     NULL
 3  Finance   1
 4  Stock     3

---

I have a similar answer - but having built it I want to post it ;)

declare @Data table (
    ID int not null,
    Name varchar(50) not null,
    ParentID int null
);

insert into @Data
select  1, 'Root', null
union select 2, 'Business', 1
union select 3, 'Finance', 1
union select 4, 'Stock', 3;

declare @UserInput int;
set @UserInput = 4;

with cParents as (
    select  d.ID, d.Name, d.ParentID
    from    @Data d
    where   d.ID = @UserInput
    union all
    select  d.ID, d.Name, d.ParentID
    from    cParents c
    inner join @Data d
        on  d.ID = c.ParentID
),
cChildren as (
    select  d.ID, d.Name, d.ParentID
    from    @Data d
    where   d.ID = @UserInput
    union all
    select  d.ID, d.Name, d.ParentID
    from    cChildren c
    inner join @Data d
        on  d.ParentID = c.ID
)
select  RecordType='self', d.ID, d.Name, ParentName=isnull(p.Name,'')
from    @Data d
left join @Data p
    on  p.ID = d.ParentID
where   d.ID = @UserInput

union all

select  RecordType='parents', d.ID, d.Name, ParentName=isnull(p.Name,'')
from    cParents d
left join @Data p
    on  p.ID = d.ParentID
where   d.ID <> @UserInput

union all

select  RecordType='children', d.ID, d.Name, ParentName=isnull(p.Name,'')
from    cChildren d
left join @Data p
    on  p.ID = d.ParentID
where   d.ID <> @UserInput;

@Data represent the sample data, @UserInput is the input variable. I added a RecordType to clarify the meaning of the record parts. It is tested on SQL Server 2008 and should work on 2005 - but not on 2000.