Sqlserver CTE how to get first non null value

Question

I am trying to use the following query to get a user with his detail and any of his manager or manager's manager id who got a record in UserTag table.

I have got the partial result, but not sure how to get the first non null value. I have show my expected result below.

With Managers AS 
( 
--initialization 
SELECT Id, Email, ManagerID, NULL as UserID
FROM dbo.[User] 
WHERE email='[email protected]'
UNION ALL 
--recursive execution 
SELECT u.Id, u.Email, u.ManagerID, tb2.UserID
FROM dbo.[User] u 
    INNER JOIN Managers m ON m.ManagerID = u.ID
    OUTER apply (select userid from UserTag where userid = m.ManagerID and (TagID=9 or TagName = 'test')) tb2
) 
SELECT * FROM Managers 

Expected result:

Sample data and expected result:

User table
=========

Id      Email           ManagerID
---     -----           ---------
11813   [email protected]  1251
1251    [email protected]  302
302     [email protected]  1221
1221    [email protected]  358
358     [email protected]  988
988     [email protected]  NULL

100     [email protected] 101
101     [email protected] 102
102     [email protected] 103
103     [email protected] 104
104     [email protected] NULL

User Tag

Id  UserId      TagId       TagName
1   1221        9           test
2   104         9           test


Expected result
==============

UserId  Email               TagManagerId
------  -----               ------------
11813   [email protected]      1221
1251    [email protected]      1221
302     [email protected]      1221

Brief description: I have all users in User table. This is a self referencing table. I want to get all users whose immediate manager or manager's manager or any manager in his/hierarchy got a record in UserTag table with tagid=9 or tagname='test' as shown here.

Schema db<>fiddle here

Answer 1

Based on your Brief description, I believe that this is an XY Problem

• There are solutions to the real need that don't include get first non null value.

What I believe you really need to do is find all the managers that are tagid=9 or tagname='test' and then recursively find all their subordinates. (Run the recursion in the opposite direction to your example query.)

The only complexity I can think of is that such a manager may have a subordinate that also meets those criteria, so I included a condition to avoid recursing them more than once.

WITH
  test9_managed_users AS
(
  SELECT
    u.*,
    u.managerid   AS closest_test9_manager_id,
    0             AS depth
  FROM
    [user]    AS u
  INNER JOIN
    UserTag   AS t
      ON  t.userid = u.managerid
      AND (t.TagID=9 or TagName = 'test')
  
  UNION ALL
  
  SELECT
    u.*,
    m.closest_test9_manager_id,
    m.depth+1
  FROM
    test9_managed_users   AS m
  INNER JOIN
    [user]                AS u
      ON u.managerid = m.id
  WHERE
    -- If a user is themselves a 'test9_manager', don't recurse their children
    -- > They would already have been included in the Anchor part of the CTE
    NOT EXISTS (
      SELECT *
        FROM UserTag  AS t
       WHERE t.userid = m.id
         AND (t.TagID=9 or TagName = 'test')
    )
)
SELECT
  *
FROM
  test9_managed_users
ORDER BY
  email

Demo with my own test data:

• https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5e901a6f7db97362bb27ebacb1d4331c

Demo with your test data:

• https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1ab3625ba797d8fc13c49872d1cd5872