Recursive SQL query only returning single reference instead of recursive references
<p>I have a DB table </p>

<pre><code>id (INT, NOT NULL)  
username (NVARCHAR, NOT NULL)  
holiday_replacement (INT, NULL)
</code></pre>

<p>The <code>holiday_replacement</code> is either <code>NULL</code> or it references a value in the <code>id</code> field.</p>

<p>Now, it would be trivial to determine for whom a specified <code>id</code> was the <em>holiday_replacement</em>, but to complicate this query the <em>holiday_replacement</em> may be chained.</p>

<p>In simpler terms, a user may have a <em>holiday_replacement</em> who themselves has a <em>holiday_replacement</em>.</p>

<p>In such a case the query should return a list of all users in this '<em>holiday replacement graph</em>'.</p>

<p>Currently I have</p>

<pre><code>WITH user_graph AS (
    SELECT
        *
    FROM
        table_name
    WHERE
        holiday_replacement = @current_user_id

    UNION ALL

    SELECT
        tn.*
    FROM
        table_name tn
        JOIN user_graph ug ON ug.holiday_replacement = tn.id
) 
SELECT
    id
FROM
    user_graph 
</code></pre>

<p>However, this query only returns those users who have <em>directly</em> referenced <code>@current_user_id</code> as their <em>holiday_replacement</em> and doesn't consider any other users who should also be returned on account of this chaining.</p>

<p>For example, if I have the following 3 users:</p>

<pre><code>id = 1, username = 'user_1', holiday_replacement = NULL  
id = 2, username = 'user_2', holiday_replacement = 1  
id = 3, username = 'user_3', holiday_replacement = 2
</code></pre>

<p>then for <code>@current_user_id</code> = 1, the query <em>should</em> return</p>

<pre><code>id
---
 2
 3
</code></pre>

<p>but presently it only considers that <em>directly referenced</em> user and returns</p>

<pre><code>id
---
 2
</code></pre>

<p>I just can't wrap my head around what I need to change. Can anyone help?</p>