SQL query to discover levels underneath
<p>I have a table structure as below. This is only a selected set of data that are directly(A,B,C,D and E) and indirectly ('B1', 'B2', 'C1', 'C2', 'C3', 'B21' and 'C31') related to 'X', to explain the question. 
The actual table has loads more records that have nothing to do with this 'X'</p>

<p>(For example there can be another Item 'Y' on the table that is made up of a 'N', 'K', 'A', 'B1', 'B21' and 'C31')</p>

<p><strong>Table1</strong></p>

<pre><code>Parent ChildP  SubCat  Qty
X      A       0       2
X      B       1       1
X      C       1       2
X      D       0       1
X      E       0       1
B      B1      0       1
B      B2      1       1
C      C1      0       1
C      C2      0       1
C      C3      1       1
B2     B21     0       1
C3     C31     0       1
Y      N       0       1
Y      K       1       2
Y      A       0       2
Y      B1      0       1
Y      B21     0       1
Y      C31     0       1
K      K1      0       0
</code></pre>

<p>I will take Parent 'X' as the example for this question.
Below is sorted to show only where <code>Parent = 'X'</code></p>

<p><strong>Table1 WHERE Parent = 'X'</strong></p>

<pre><code>Parent ChildP SubCat  Qty
X       A       0      2
X       B       1      1
X       C       1      2
X       D       0      1
X       E       0      1
</code></pre>

<p>Now in this table where <code>ChildPs</code> with <code>SubCat</code> as 1 has their own <code>childP</code>s underneath.</p>

<ul>
<li><p>Ex: <code>SELECT * FROM Table1 WHERE Parent = 'B'</code></p>

<pre><code>Parent ChildP SubCat Qty
B      B1     0       1
B      B2     1       1
</code></pre></li>
<li><p>Ex: <code>SELECT * FROM Table1 WHERE Parent = 'C'</code></p>

<pre><code>Parent ChildP SubCat Qty
C      C1     0       1
C      C2     0       1
C      C3     1       1
</code></pre></li>
</ul>

<p>What I want my result set to look like is something like below, showing everything that X is made up from not just the top level <code>ChildP</code>s,</p>

<pre><code>Parent Level FROM  ChildP SubCat  Qty
X      1     X     A      0       2
X      1     X     B      1       1
X      1     X     C      1       2
X      1     X     D      0       1
X      1     X     E      0       1
X      2     B     B1     0       1
X      2     B     B2     1       1
X      2     C     C1     1       1
X      2     C     C2     0       1
X      2     C     C3     0       1
</code></pre>

<p>These B2 and C1 will have further children underneath them and these need showing as well, and ideally the final result would look like below:</p>

<pre><code>Parent Level FROM  ChildP SubCat  Qty
X      1     X     A      0       2
X      1     X     B      1       1
X      1     X     C      1       2
X      1     X     D      0       1
X      1     X     E      0       1
X      2     B     B1     0       1
X      2     B     B2     1       1
X      2     C     C1     1       1
X      2     C     C2     0       1
X      2     C     C3     0       1
X      3     B2    B2A    0       1
X      3     C1    C1A    0       1
</code></pre>

<p>Now this B2A and C1A might have further levels underneath, down to about 5-6 levels. My issue is, that I would not know how many levels each <code>ChildP</code> goes to initially looking at a Parent (Ex:X) </p>

<p>It's like saying: Show everything where Parent is X and having an if clause to say if <code>SubCat</code> is '1' look further until <code>SubCat</code> is '0'</p>

<p>Is this possible with a SQL Query ?</p>