SQL parents and children

Transact-SQL
1

I have a couple of questions, is there a better way of writing this and can I get the level to populate automatically as currently I have the query going to Level 8

Basically I have a parent HOBWT03 and want to find all the childern.

Table:

image
image198×31461 1.14 MB

2

Please provide DDL, Sample data and expected results. Looks like your table is providing what you want

3

So basically I want to select a location(child or parent) and see all its children

image
image1099×481 10 KB

4

As requested Please provide sample data as DDL and DML as follows. This is a sample you will need to provide your own for the question you are asking. Help us help you

--the following is DDL Data Definition Language
create table #jeevad(Documenttype varchar(50),
 Filepath varchar(50),Remarks varchar(50), Filename varchar(50),
DocumentNo int, Hijri  nvarchar(50),Gregorian nvarchar(50), issuedate date)

--the following is DML Data Manipulation Language
insert into #jeevad
select 'CR Certificate SAB General Contracting.', 
'c:/filename test', NULL, 123, NULL, NULL, NULL union

--etc etc etc