Query pb

hi  trifon, add 1 condition to javed's query

SELECT distinct A.id, a.name
FROM stores As A
INNER JOIN stores As B On (A.id = B.parentId) and b.id=2 and a.siteTypeId = 2

it is very near to what I want to receive. I forgot that in my table there is one more column
create table stores(
id int primary key,
name nvarchar,
parentId int,
headofficeId int,
siteTypeId int)

I have E.g.

1, 'A HO', 0, 0, 2,
2, 'A1',1,1, 1,
3,'A2',1,1, 5,
4,'B HO',0,0, 2,
5, 'B',4,1, 3,
6,'B1'4,2, 4

I want to have query, which gives me the id and name of 'A HO' when I read record 3 or record 2, the same way to see 'B HO' when I read record 5 or 6. Where siteTypeId = 2 is Head office, 3 -  is independent, 5 -  is test, 4 - is reseller, 1 - is group and A1, A2, ... may have HO (head office) or not

try this...

SELECT distinct A.id, a.name
FROM stores As A
INNER JOIN stores As B On (A.id = B.parentId) and b.id=2;