Recursive SELECT using JOIN from 2 tables

Question

I have 2 tables: Users and Roles.

Users table columns:

UserId  FirstName  Lastname

Roles table columns:

RoleId  UserId  ParentId

I want to be able to fetch date from these 2 tables representing a column named ParentName.

So lets say I have these data:

Users table:

UserId  FirstName  Lastname
1       John       Doe
2       Jane       Smith
3       John       Smith

Roles table:

RoleId  UserId  ParentId
1       1       NULL
2       2       1
3       3       2

So I want to have this table:

UserId  FirstName  Lastname   RoleId   ParentId   ParentName 
1       John       Doe        1        NULL       NULL
2       Jane       Smith      2        1          John Doe
3       John       Smith      3        2          Jane Smith

I tried but I couldn't. I tried INNER JOIN, OUTER JOIN, Subqueries but I Couldn't get what I wanted.

In joining 2 tables together I have this:

SELECT UserId, FirstName, Lastname, RoleId, ParentId
FROM Users INNER JOIN Roles ON Users.UserId = Roles.UserId

And In joining a self recursive Roles table I have this:

SELECT ChildUsers.UserId, ChildUsers.RoleId, ParentUsers.UserId, ParentUsers.RoleId, Users.LastName
FROM Roles AS ChildUsers
LEFT JOIN Roles AS ParentUsers ON ChildUsers.ParentId = ParentUsers.RoleId 
INNER JOIN Users ON Users.UserId = ParentUsers.UserId

But I couldn't get what I wanted. How can I get that?

-----------UPDATE---------------

Here is my own solution:

SELECT Roles.RoleID, LastName, Parent.ParentName FROM
Roles INNER JOIN Users ON Users.UserID = Roles.UserID LEFT JOIN
(SELECT LastName AS ParentName, RoleID FROM Users INNER JOIN Roles ON Users.UserID = Roles.UserID) Parent ON Parent.RoleID = Roles.ParentID

But I choose LukStorms solution as the answer.

Answer 1

To get that expected result?

If the ParentId contains RoleId's then this should work:

SELECT 
  r.UserId, 
  u.FirstName, 
  u.Lastname, 
  r.RoleId, 
  r.ParentId, 
  pu.FirstName+' '+pu.LastName AS ParentName 
FROM Roles r
LEFT JOIN Users u ON r.UserId = u.UserId
LEFT JOIN Roles pr on r.ParentId = pr.RoleId
LEFT JOIN Users pu on pr.UserId = pu.UserId

But if the ParentId would be a foreign key to the Users table:

SELECT 
  r.UserId, 
  u.FirstName, 
  u.Lastname, 
  r.RoleId, 
  r.ParentId, 
  p.FirstName+' '+p.LastName AS ParentName
FROM Roles r
LEFT JOIN Users u ON r.UserId = u.UserId
LEFT JOIN Users p on r.ParentId = p.UserId

Answer 2

Using this query you can get the result you want. Since you only want a 2 level depth, you can do it without recursive queries

DECLARE @Users TABLE (UserId INT, FirstName VARCHAR(255), LastName VARCHAR(255))
INSERT INTO @Users VALUES
(1,'John','Doe'),
(2,'Jane','Smith'),
(3,'John','Smith')

DECLARE @ROles TABLE (RoleId INT, UserId INT, ParentId INT)
INSERT INTO @ROles VALUES
(1,1,NULL),
(2,2,1),
(3,3,2)

SELECT  u.UserId, u.FirstName, u.LastName, r.RoleId, r.ParentId, p.ParentName
FROM    @Users AS u
    LEFT OUTER JOIN @Roles AS r ON r.UserId = u.UserId
    LEFT OUTER JOIN
        (SELECT r.UserId AS ParentId, up.FirstName + ' ' + up.LastName AS ParentName
         FROM   @ROles AS r 
            INNER JOIN
                @Users AS up ON up.UserId = r.ParentId) AS p ON p.ParentId = u.UserId

Update: add result

UserId  FirstName LastName  RoleId  ParentId ParentName
1       John      Doe       1       NULL     NULL
2       Jane      Smith     2       1        John Doe
3       John      Smith     3       2        Jane Smith

Answer 3

Try this query:-

Create table Users (UserId INT, FirstName VARCHAR(255), LastName 
VARCHAR(255));
INSERT INTO Users VALUES(1,'John','Doe');
INSERT INTO Users VALUES(2,'Jane','Smith');
INSERT INTO Users VALUES(3,'John','Smith');

Create table Roles(RoleId INT, UserId INT, ParentId INT);
INSERT INTO ROles VALUES(1,1,NULL);
INSERT INTO ROles VALUES(2,2,1);
INSERT INTO ROles VALUES(3,3,2);

SELECT a.*,concat(b.FirstName,' ',b.Lastname) as ParentName
from 
(
SELECT a.UserId, FirstName, Lastname, RoleId, ParentId
FROM Users a INNER JOIN Roles b ON a.UserId = b.UserId
) a
left join
Users b
on a.ParentId=b.userid;

Output:-

    UserId  FirstName   Lastname    RoleId  ParentId    ParentName
1   1           John    Doe            1    NULL     
2   2           Jane    Smith          2    1            John Doe
3   3           John    Smith          3    2            Jane Smith

Answer 4

Code explained:

CTE to get the user details and roles.

LEFT JOIN with the CTE to get the parent's information. Joining by the user's ParentId with the Parents's (who is also a user) UserId to get the ParentName.

(Note: You can run the cte separtely without the second join to see the output. Then run the whole to see how it is joining.)

CREATE TABLE [dbo].[Roles](
    [RoleId] [int] NULL,
    [UserId] [int] NULL,
    [ParentId] [int] NULL
)
;
CREATE TABLE [dbo].[Users](
    [UserId] [int] NULL,
    [FirstName] [varchar](20) NULL,
    [Lastname] [varchar](20) NULL
);



INSERT [dbo].[Roles] ([RoleId], [UserId], [ParentId]) VALUES (1, 1, NULL);
INSERT [dbo].[Roles] ([RoleId], [UserId], [ParentId]) VALUES (2, 2, 1);
INSERT [dbo].[Roles] ([RoleId], [UserId], [ParentId]) VALUES (3, 3, 2);

INSERT [dbo].[Users] ([UserId], [FirstName], [Lastname]) VALUES (1, N'John', N'Doe');
INSERT [dbo].[Users] ([UserId], [FirstName], [Lastname]) VALUES (2, N'Jane', N'Smith');
INSERT [dbo].[Users] ([UserId], [FirstName], [Lastname]) VALUES (3, N'John', N'Smith');


with cteUsers AS
(
    select u.UserId, u.FirstName, u.Lastname, r.RoleId, r.ParentId
    from Users AS u
    inner join Roles AS r
        on u.UserId=r.UserId
)
select 
     cteUsers.UserId
    ,cteUsers.FirstName 
    ,cteUsers.Lastname  
    ,cteUsers.RoleId    
    ,cteUsers.ParentId
    ,(p.FirstName + ' ' + p.Lastname) AS ParentName
from cteUsers
left join Users as p
    on cteUsers.ParentId=p.UserId
;

Run this script live