How to Get Parent with Child Products or Siblings Products?

Question

I have a Product Table which is only one level deep. Means, I don't have child of child. My table structure sample is,

ID Name ParentID
----------------
1   A     NULL
2   B     1
3   C     NULL
4   D     3
5   E     1

Now my input is ID = 1, it should give me,

ID Name ParentID
----------------
1   A     NULL
2   B     1
5   E     1

if input is ID = 2, it should give me,

ID Name ParentID
----------------
1   A     NULL
2   B     1
5   E     1

if input is ID = 3, it should give me,

ID Name ParentID
----------------
3   C     NULL
4   D     1

Because 2 don't have any children, I selected the siblings

Answer 1

Please try:

declare @var int
set @var=2

select top 1 @var=ISNULL(Parentid, id) from YourTable where ID=@var

select 
  * 
From 
  YourTable 
where
  ID=@var

union all

select 
  * 
From 
  YourTable 
where
  ParentID=@var

SQL Fiddle Demo