Recursive query to select Parent and Child cases in sql

0
votes

I'm trying to implement recursion on query using Common Table Expression. However I'm not very much familiar with it. The scenario surrounds between parent and child cases where Parent when logged in can see his own as well as his all children cases and so on and so forth. But I'm unable to do this with using cte expression.

I have a user table and a relation table that contains both the primary and secondary user. The primary user is parent and secondary user is child.

Below is my query.

select *
from dbo.[Case] c
left join dbo.[User] u on c.AssignedTo = u.UserId
left join dbo.[User] uu on c.AssignedTo = uu.UserId
where uu.UserId in (select SecondaryUser from  dbo.[Relations]
                    where PrimaryUser = 'f422233f-9c70-4052-81a5-e2525707df0b') 
    or u.UserId = 'f422233f-9c70-4052-81a5-e2525707df0b'

But the above query only returns one parent and one child case. And i want to have multiple parents and their multiple child case using common table expression.

Assuming some users as follows

    Id   Name   Email 
    001  Salar  [email protected]
    002  Ather  [email protected]
    003  John   [email protected]

And In RelationShip Table

Id   PrimaryUser SecondaryUser
101    001       002
001    002       003

And their Assigned Cases

Id     CaseTitle    CaseDescription  AssingedTo
101    Case 1       First case    001
102    Case 2       Second case   002
103    Case 3       Third case    003

So when i logged in to 001 id I should see all three cases, and when i logged in with 002 i should see last two cases.

2
sqlcommon-table-expression
Please provide sample data and desired results. – Gordon Linoff
Please see the edited questions @GordonLinoff – Salar Muhammad

2 Answers

1
votes

This query will return all user ids in relation to first id. You can join result with cases:

declare @Users as table
    (UserId  int)
;

declare @Relations as table
    (PrimaryUser  int, SecondaryUser int)
;    

INSERT INTO @Relations
    (PrimaryUser, SecondaryUser)
VALUES
    (1,2),
    (1,3),
    (2,4),
    (2,7),
    (2,8),
    (5,6),
    (6,19)

INSERT INTO @Users
    (UserId)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (7),
    (5),
    (6),
    (19),
    (20)

;WITH cte1 AS (
  SELECT UserId AS [User]
  FROM @Users 
  WHERE UserId = 5
  GROUP BY UserId
UNION ALL  
  SELECT SecondaryUser  AS [User]
  FROM cte1
  JOIN @Relations t 
    ON t.PrimaryUser = cte1.[User]   
)
SELECT [User] FROM cte1
0
votes

Here is a very simple example of a hierarchical CTE:

with t(level,path,boss,name) as 
(
        -- this is the starting point (can be one or more records)
        select 0,name,boss,name from @empl where boss is null

        union all

        -- here you construct the tree (via the join conditions)
        select
            level + 1,
            path + ' > ' + e.name,
            e.boss, e.name 
        from @empl e
            inner join t on e.boss = t.name                
) 
-- here you collect the results from the cte
select * 
from t 
order by path;

Cf. https://gist.github.com/pedroreys/8336d6f4dcb63ba619c0